Grouping Columns

[FaizShaikh]

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!!

[m_dekorte · Accepted solution]

@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

[dufoq3 · Accepted solution]

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

[m_dekorte]

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

[FJS]

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.

[m_dekorte · Accepted solution]

@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

[FJS]

No Code.

Solution without Mquery or any other code please.

[m_dekorte]

@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...