Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sravani9920
Frequent Visitor

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

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

Hi @sravani9920 cifferent approach here.

 

Result:

dufoq3_0-1709397211023.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

AlienSx
Super User
Super User

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

from.jpgto.jpg

 

sravani9920_0-1708678726175.png

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

sravani9920_1-1708678816141.png

Please have a look on it.

 

@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
Super User
Super User

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

m_dekorte_0-1708673674890.png

I hope this is helpful

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors