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