Unpivoting Issue

[sravani9920]

Hello Everyone,

My data as below,

name	age	sub1	marks1	sub2	marks2
a	15	ENG	50	Hindi	48
b	18	MATH	45	Science	50

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.

name	age	sub	marks
a	15	ENG	50
a	15	Hindi	48
b	18	MATH	45
b	18	Science	50

[dufoq3]

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

[AlienSx]

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]

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

Please have a look on it.

[AlienSx]

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

[m_dekorte]

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 · Accepted solution]

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.