Search found 16 matches

by Rudi
Thu Jul 11, 2019 3:38 pm
Forum: Power Query
Topic: Standardize Date formats
Replies: 4
Views: 663

Re: Standardize Date formats

That could be a problem. It would become very difficult to distinguish normal text from dates if they have spaces. I'm not saying its impossible, but its beyond my ability to assist. If you don't get additional help from this forum, try posting in other PQ forums on the web and link to this post to ...
by Rudi
Fri Jun 28, 2019 5:51 pm
Forum: Power Query
Topic: Trasform grouped header in a proper table [Solved]
Replies: 2
Views: 370

Re: Trasform grouped header in a proper table [Solved]

Hi, How about this: trasform in a table.xlsx M-Code let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Transposed Table" = Table.Transpose(Source), #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}), #"Promoted Heade...
by Rudi
Tue May 28, 2019 8:13 pm
Forum: Power Query
Topic: Standardize Date formats
Replies: 4
Views: 663

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: 710

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: 724

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: 810

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: 810

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: 810

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: 810

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: 448

Re: 2nd largest value

Another option:

= List.Skip(List.MaxN(Source, 2),1)

Go to advanced search

cron