Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Turn rows into columns with Distinct

Hello, everyone 🙂 My table follows this structure: ID      Level   Code   111 D 53.1 111 K 75.1 222 H 82.1 333 G 75.1 333 G 82.1   I would like to create a new t...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    6 years ago

    Anonymous 

    In Power Query, Select LEVEL Column, go to  Transform Tab and  Click on Pivot Column.
    In the Value Column, choose CODE, It will pivot as you expected




    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

     

westwrightj's avatar
westwrightj
Resolver III
6 years ago

Hey Anonymous 

 

   I think I might have a workable solution

 

I've use the same data as you for testing

 

 

You should be able to perform this one the Power Query side by using a combination of grouping, a little M manipulation, and a pivot.

 

I've gone ahead and copied the steps I've taken below from the advanced editor and the output I had gotten

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRcgFiU2M9Q6VYHZiQNxCbm0KFjIyMgFwPILYwggoZGxsDue7IqhBCEFWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Level = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Level", type text}, {"Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Unique ID", each [ID]&"_"&[Level]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID", "Level"}, {{"Values", each Text.Combine([Code], "/"), type nullable text}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Level]), "Level", "Values")
in
    #"Pivoted Column"

 

 

First start by performing a group by but you will need to change the M line that is automatically entered from List.Sum over to what I've included in my steps as "Text.Combine". This will account for the multiple values in an individual column.

 

The only thing with this solution is that those two values are being treated as text. If you want them to be numerical you may require a dax-forward solution.