Search found 14 matches

by Rudi
Tue May 28, 2019 8:13 pm
Forum: Power Query
Topic: Standardize Date formats
Replies: 2
Views: 254

Re: Standardize Date formats

This custom function appears to work on your sampling. I copied your sampling into Excel and loaded it into PQ I copied the code for the custom function into a new blank query Back in the sampling query, I invoked a custom function, selected the function and pointed it to the column containing the d...
by Rudi
Tue May 21, 2019 9:52 pm
Forum: Power Query
Topic: Group and sum delimited values 2
Replies: 1
Views: 533

Re: Group and sum delimited values 2

Like this: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Data = Table.Group(Source, {"ColumnA"}, { {"new", each Text.Combine(List.Transform(List.Zip(List.Transform([ColumnB], each List.Transform(Text.Split(_, ";"), each Number.From(_)))), eac...
by Rudi
Mon May 20, 2019 8:02 pm
Forum: Power Query
Topic: Replace value in single cell
Replies: 4
Views: 469

Re: Replace value in single cell

If its a lone value in a column, you can use: = Table.ReplaceValue(<Previous Step>,"Old Value","New Value",Replacer.ReplaceValue,{"Column2"}) If its a value in a specific row, you will need to first isolate that row using a filter or a row extract (eg: using Keep Row Ra...
by Rudi
Mon May 20, 2019 5:06 pm
Forum: Power Query
Topic: Sum of a column
Replies: 8
Views: 414

Re: Sum of a column

Like this: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Total = List.Sum(Source[Data 3]), #"Grouped Rows" = Table.Group(Source, {"Data 1"}, {{"Group Sum", each List.Sum([Data 3]), type number}, {"Data", each _, type table [Data 1=t...
by Rudi
Thu May 16, 2019 10:27 pm
Forum: Power Query
Topic: Sum of a column
Replies: 8
Views: 414

Re: Sum of a column

I don't understand your second question?
Sorry!
by Rudi
Thu May 16, 2019 10:26 pm
Forum: Power Query
Topic: Sum of a column
Replies: 8
Views: 414

Re: Sum of a column

You can create a new column based on the Total created by the Total Step.

eg:

= Table.AddColumn(#"Create Calc", "Total Value", each Total)
by Rudi
Wed May 15, 2019 5:05 pm
Forum: Power Query
Topic: Sum of a column
Replies: 8
Views: 414

Re: Sum of a column

How about this: (See comments in the M code.) let //Connect to 2 column table source representing values you posted. //Column 1 titled: "Data 1"; Column 2 titled: Data 2 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], //Sum up [Data 2] and store value in Step: Total T...
by Rudi
Wed May 15, 2019 4:20 pm
Forum: Power Query
Topic: 2nd largest value
Replies: 3
Views: 223

Re: 2nd largest value

Another option:

= List.Skip(List.MaxN(Source, 2),1)
by Rudi
Fri Apr 13, 2018 9:03 pm
Forum: Power Query
Topic: Help needed for Data Transformation in Powerquery
Replies: 2
Views: 1551

Re: Help needed for Data Transformation in Powerquery

My 2 cents contribution Use the code below on this source file: Data.zip let Source = Csv.Document(File.Contents("C:\Data.txt"),[Delimiter=" ", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1&...
by Rudi
Fri Apr 13, 2018 8:35 pm
Forum: Power Query
Topic: Check all columns
Replies: 3
Views: 1262

Re: Check all columns

See this useful article on the subject by Chris Webb.

Go to advanced search

cron