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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors