Forum Discussion

FaizShaikh's avatar
FaizShaikh
Regular Visitor
2 years ago
Solved

Grouping Columns

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 w...
Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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's avatar
    dufoq3
    2 years ago

    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's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
2 years ago

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's avatar
FJS
Regular Visitor
2 years ago

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's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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's avatar
      FJS
      Regular Visitor
      2 years ago

      No Code.

       

      Solution without Mquery or any other code please.

      • m_dekorte's avatar
        m_dekorte
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

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