topic Re: Grouping Columns in Power Query

Grouping Columns

FaizShaikh — Wed, 13 Mar 2024 19:24:12 GMT

Hi,

It might be a stupid question. I tried but not able to find the perfect result.

I am trying to convert multiple columns into group rows in power query.

Below are the examples.

Data which I have (its a sample I have lot of columns after new department) :

Name	Old City	New City	Old Department	New Department
James	New York	Columbus	Finance	Procurement
Harry	Mesa	Dallas	Procurement	Analytics

Data (Result) which I want in power query:

Name	Change type	Old Value	New Value
James	City	New York	Columbus
James	Department	Finance	Procurement
Harry	City	Mesa	Dallas
Harry	Department	Procurement	Analytics

Attaching table's snippet for more clarity.

Thanks in Advance for your help!!

Re: Grouping Columns

m_dekorte — Wed, 13 Mar 2024 19:56:07 GMT

Hi FaizShaikh,

There are many ways to achieve this, here's a mostly UI approach.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYq7CoAwDAB/RTL3J0QRERRXKR1iyVDsA5IW8e+Nq9Mdx1kLCyYSMLDR3R2FL9WhxJbO9tUpZMye1HYuvjElyhWcsTAj86N9JUHFiDGi/D4Dfcb41OAFnHsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Old City" = _t, #"New City" = _t, #"Old Department" = _t, #"New Department" = _t]), UnpivotOthers = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"), TextAfterDelimiter = Table.TransformColumns(UnpivotOthers, {{"Attribute", each Text.AfterDelimiter(_, " "), type text}}), GroupRows = Table.Group(TextAfterDelimiter, {"Name", "Attribute"}, {{"t", each Record.FromList( _[Value], {"Old Value", "New Value"}) }}), ExpandFields = Table.ExpandRecordColumn(GroupRows, "t", {"Old Value", "New Value"}, {"Old Value", "New Value"}) in ExpandFields

I hope this is helpful

Re: Grouping Columns

FJS — Wed, 13 Mar 2024 20:38:35 GMT

Hi m_dekorte ,

Thanks for the reply.

If it is possible can you show me in other ways.

It would be best if you can show in the way which have no coding in it.

As my client might also need to do this in future by himself.

Re: Grouping Columns

m_dekorte — Wed, 13 Mar 2024 21:26:09 GMT

FaizShaikh sure there is, give this a go.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYq7CoAwDAB/RTL3J0QRERRXKR1iyVDsA5IW8e+Nq9Mdx1kLCyYSMLDR3R2FL9WhxJbO9tUpZMye1HYuvjElyhWcsTAj86N9JUHFiDGi/D4Dfcb41OAFnHsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Old City" = _t, #"New City" = _t, #"Old Department" = _t, #"New Department" = _t]), UnpivotOthers = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"), SplitByDelimiter = Table.SplitColumn(UnpivotOthers, "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}), MergeColumns = Table.CombineColumns(SplitByDelimiter,{"Name", "Attribute.2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), PivotColumn = Table.Pivot(MergeColumns, List.Distinct(MergeColumns[Attribute.1]), "Attribute.1", "Value"), SplitColumn = Table.SplitColumn(PivotColumn, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"City", "Change Type"}) in SplitColumn

I hope this is helpful

Re: Grouping Columns

FJS — Wed, 13 Mar 2024 23:30:34 GMT

No Code.

Solution without Mquery or any other code please.

Re: Grouping Columns

m_dekorte — Thu, 14 Mar 2024 06:00:23 GMT

FaizShaikh,

Nothing happens by itself. What are you proposing?

Understand that this M code was generated through interaction with the User Interface, something you should be able to explain to a client. Basically, it boils down to a pattern of pushing buttons, nothing more...

Re: Grouping Columns

dufoq3 — Sun, 17 Mar 2024 14:07:04 GMT

Hi FJS, you should try by yourself but one different approach without merging and grouping here:

Result

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYq7CoAwDAB/RTL3J0QRERRXKR1iyVDsA5IW8e+Nq9Mdx1kLCyYSMLDR3R2FL9WhxJbO9tUpZMye1HYuvjElyhWcsTAj86N9JUHFiDGi/D4Dfcb41OAFnHsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Old City" = _t, #"New City" = _t, #"Old Department" = _t, #"New Department" = _t]), UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Name"}, "Change type", "Value"), Ad_OldNew = Table.AddColumn(UnpivotedOtherColumns, "OldNew", each if Text.StartsWith([Change type], "old", Comparer.OrdinalIgnoreCase) then "Old Value" else "New Value", type text), ExtractedTextAfterDelimiter = Table.TransformColumns(Ad_OldNew, {{"Change type", each Text.AfterDelimiter(_, " "), type text}}), PivotedColumn = Table.Pivot(ExtractedTextAfterDelimiter, List.Distinct(ExtractedTextAfterDelimiter[OldNew]), "OldNew", "Value") in PivotedColumn