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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Curti93
Frequent Visitor

rearrange table by shifting certain rows of columns to new generic columns

Hello community,

 

I need some help..I already tried some Pivots or transpose functionalities but it doesn't really get where I need it to be.

So, here is my input table called "TYPE"

    ID    

         Day         

  Hours  

  Type  

123

01.12.2022

  3

  A

123

01.12.2022

  1

  B

123

01.12.2022

  3

  C

123

02.12.2022

  0,5

  C

124

02.12.2022

  5

  D

125

10.12.2022

  2

  B

125

10.12.2022

  3

  C

 

Now I am trying to get it into a format like this, where I have "generic" column names and the values are being "inserted" from left to right, independet of which type it is:

 

ID     

        Day         

    Type1Desc    

    Type1  

    Type2Desc   

    Type2    

    Type3Desc    

   Type3   

123

01.12.2022

        A

        3

        B

        1

        C

        3

123

02.12.2022

        C

        0,5

 

 

 

 

124

02.12.2022

        D

        5

 

 

 

 

125

10.12.2022

        B

        2

        C

        3

 

 

 

Do you have any idea, how to make this happen in PQ?

 

If you need any further details, please let me know!


Thanks you very much in advance 🙂

1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

Hi @Curti93 
you need a nested index for the column name pairs: NestedIndex in PowerBI - YouTube
Then you can follow this description: Pivot multiple measures and columns in Power Query (thebiccountant.com)
This is a full code example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjDUMzTSMzIwMgJyDi1QMIZQjkqxOriUGEIoJzxKoKY4IysxQlVioGOKqsgEiyKoEheoEhDX0ABViRGqa7ApQbgmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Day = _t, Hours = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Day", type date}, {"Hours", Int64.Type}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Day"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1)}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Hours", "Type", "Index"}, {"Hours", "Type", "Index"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Partition", {"Hours", "Type"}, "Attribute", "Value"),
    #"Added Prefix" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Index", each "Type " & Text.From(_, "en-US"), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Added Prefix",{"Index", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Community Champion
Community Champion

Hi @Curti93 
you need a nested index for the column name pairs: NestedIndex in PowerBI - YouTube
Then you can follow this description: Pivot multiple measures and columns in Power Query (thebiccountant.com)
This is a full code example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjDUMzTSMzIwMgJyDi1QMIZQjkqxOriUGEIoJzxKoKY4IysxQlVioGOKqsgEiyKoEheoEhDX0ABViRGqa7ApQbgmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Day = _t, Hours = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Day", type date}, {"Hours", Int64.Type}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Day"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1)}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Hours", "Type", "Index"}, {"Hours", "Type", "Index"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Partition", {"Hours", "Type"}, "Attribute", "Value"),
    #"Added Prefix" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Index", each "Type " & Text.From(_, "en-US"), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Added Prefix",{"Index", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Curti93
Frequent Visitor

Thank you so much for your quick response, that's very impressive! 🙂
Out of curiosity: How did you approach this situation? What was the "hint" so that you knew, I needed a nested index for the column pairs?

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.