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

    • m_dekorte's avatar
      m_dekorte
      Resident 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...