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 which I have (its a sample I have lot of columns after new department) :

NameOld CityNew CityOld Department

New Department

JamesNew YorkColumbusFinanceProcurement
HarryMesaDallasProcurementAnalytics

 

Data (Result) which I want in power query:

NameChange typeOld ValueNew Value
JamesCityNew YorkColumbus
JamesDepartmentFinanceProcurement
HarryCityMesaDallas
HarryDepartmentProcurementAnalytics

 

Attaching table's snippet for more clarity.

 

 

Thanks in Advance for your help!!

 

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

6 Replies

  • 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

  • dufoq3's avatar
    dufoq3
    Community Champion
    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