Forum Discussion

sravani9920's avatar
sravani9920
Frequent Visitor
2 years ago
Solved

Unpivoting Issue

Hello Everyone,

 

My data as below,

nameagesub1marks1sub2marks2
a15ENG50Hindi48
b18MATH45Science50

 

The below is the Output format , To get it I worked with unpivot and Grouping in power Query, But it's resulting incorrect result,  could you please help me anyone to resolve this, Thank you in advance.

 

nameagesubmarks
a15ENG50
a15Hindi48
b18MATH45
b18Science50
Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi sravani9920,

     

    To answer your question, here's a fully User Interface driven approach:

    let
    Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTVXSUUpMB5HFpUmGQCo3sSi72BDCN4LxjZRidaKVEoFcQ1Mg4ernDiRNDYCER2ZeSiaQNrEAK0kCKbEAEr6OIR4gYZDy4OTM1LzkVIiW2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t])),
    MergeSet1 = Table.CombineColumns(Source,{"sub1", "marks1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    MergeSet2 = Table.CombineColumns(MergeSet1,{"sub2", "marks2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged.1"),
    UnpivotOthers = Table.UnpivotOtherColumns(MergeSet2, {"name", "age"}, "Attribute", "Value"),
    RemoveCols = Table.RemoveColumns(UnpivotOthers,{"Attribute"}),
    SplitCols = Table.SplitColumn(RemoveCols, "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"sub", "marks"})
in
    SplitCols

     It merges each set of values, manually, before unpivotting them.

     

    Currently, your dummy data shows that each set shares the same number (like sub1 and marks1) that means insted of performing two manual column merges. We can adapt the solution to deal with any number of sets by automatically combining columns that share the same suffix in the column name.

    MergeGrades = List.Accumulate( List.Select( Table.ColumnNames(Source), each Text.StartsWith( _ , "sub")), Source, (s, a)=> Table.CombineColumns(s ,{a, "marks" & Text.AfterDelimiter(a, "sub")},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged" & Text.AfterDelimiter(a, "sub")))

    That is what this step does in the initial solution, I provided.

     

    I hope this is helpful.

6 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    Hi sravani9920 ,

     

    You can try something like this:

    let
    Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTVXSUUpMB5HFpUmGQCo3sSi72BDCN4LxjZRidaKVEoFcQ1Mg4ernDiRNDYCER2ZeSiaQNrEAK0kCKbEAEr6OIR4gYZDy4OTM1LzkVIiW2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t])),
    MergeGrades = List.Accumulate( List.Select( Table.ColumnNames(Source), each Text.StartsWith( _ , "sub")), Source, (s, a)=> Table.CombineColumns(s ,{a, "marks" & Text.AfterDelimiter(a, "sub")},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged" & Text.AfterDelimiter(a, "sub"))),
    UnpivotOthers = Table.UnpivotOtherColumns(MergeGrades, {"name", "age"}, "Attribute", "Value"),
    RemoveCols = Table.RemoveColumns(UnpivotOthers,{"Attribute"}),
    SplitCols = Table.SplitColumn(RemoveCols, "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"sub", "marks"})
in
    SplitCols

    That returns this result

    I hope this is helpful

    • m_dekorte's avatar
      m_dekorte
      Resident Rockstar
      2 years ago

      Hi sravani9920,

       

      To answer your question, here's a fully User Interface driven approach:

      let
    Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTVXSUUpMB5HFpUmGQCo3sSi72BDCN4LxjZRidaKVEoFcQ1Mg4ernDiRNDYCER2ZeSiaQNrEAK0kCKbEAEr6OIR4gYZDy4OTM1LzkVIiW2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t])),
    MergeSet1 = Table.CombineColumns(Source,{"sub1", "marks1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    MergeSet2 = Table.CombineColumns(MergeSet1,{"sub2", "marks2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged.1"),
    UnpivotOthers = Table.UnpivotOtherColumns(MergeSet2, {"name", "age"}, "Attribute", "Value"),
    RemoveCols = Table.RemoveColumns(UnpivotOthers,{"Attribute"}),
    SplitCols = Table.SplitColumn(RemoveCols, "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"sub", "marks"})
in
    SplitCols

       It merges each set of values, manually, before unpivotting them.

       

      Currently, your dummy data shows that each set shares the same number (like sub1 and marks1) that means insted of performing two manual column merges. We can adapt the solution to deal with any number of sets by automatically combining columns that share the same suffix in the column name.

      MergeGrades = List.Accumulate( List.Select( Table.ColumnNames(Source), each Text.StartsWith( _ , "sub")), Source, (s, a)=> Table.CombineColumns(s ,{a, "marks" & Text.AfterDelimiter(a, "sub")},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged" & Text.AfterDelimiter(a, "sub")))

      That is what this step does in the initial solution, I provided.

       

      I hope this is helpful.

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hello, sravani9920 

     

    let
    Source = your_data,
    to_list = List.Buffer(Table.ToList(Source, (x) => x)),
    tra = List.TransformMany(
        to_list, 
        (x) => List.Split(List.Skip(x, 2), 2),
        (x, y) => List.FirstN(x, 2) & y

    ),
    to_table = Table.FromList(tra, (x) => x, {"name", "age", "sub", "marks"})
in
    to_table

     

    • sravani9920's avatar
      sravani9920
      Frequent Visitor
      2 years ago

      The above is the code I used, and the below is the result I get

      Please have a look on it.

       

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        sravani9920 looks like Source does not correspond to the one from your first message. I did not expect you to do any column selections and/or expand table column manipulations. 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi sravani9920 cifferent approach here.

     

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0BRKufu5A0tQASHhk5qVkAmkTC6VYnWilJJASCyDh6xjiARIGKQ9OzkzNS06FaImNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, age = _t, sub1 = _t, marks1 = _t, sub2 = _t, marks2 = _t]),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"name", "age"}, "Attribute", "Value"),
    ReplaceAttributeColumn = Table.TransformColumns(UnpivotedOtherColumns, {{"Attribute", each Text.Remove(_, {"0".."9"})}}),
    DistinctAttributes = List.Buffer(List.Distinct(ReplaceAttributeColumn[Attribute])),
    StepBack = ReplaceAttributeColumn,
    PivotedColumn = Table.Pivot(StepBack, DistinctAttributes, "Attribute", "Value", each _),
    Ad_Combined = Table.AddColumn(PivotedColumn, "Combined", each List.Transform(List.Zip(Record.ToList(Record.SelectFields(_, DistinctAttributes))), (x)=> Text.Combine(x, "||")) ),
    RemovedColumns = Table.RemoveColumns(Ad_Combined, DistinctAttributes),
    ExpandedCombined = Table.ExpandListColumn(RemovedColumns, "Combined"),
    SplitColumnByDelimiter = Table.SplitColumn(ExpandedCombined, "Combined", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), DistinctAttributes)
in
    SplitColumnByDelimiter