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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abukapsoun
Post Patron
Post Patron

Unpivot Table

Hi,

 

I have a bit of strange request not sure if it is possible but I am giving it a try. 

 

I have the following table,

 

 Capture.PNG

 

Is there a way I can duplicate the row, and put technology-2 under technology-1, product-2 under product-1? 

In other way, can I have a single column for " Product", "Technology" and "Skill" and populating tech-1 and tech-2 under it?

So in that case, each single row will have two rows. 

 

thanks,

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

It is not such a strange request; I have seeen such requests before.

 

I assume you have other columns in your table as well, so I added a "Name" column that represents all those columns.

 

Steps:

  • Select all columns that are not displayed in your picture
  • Choose Transform - Unpivot - Other Columns
  • Select column "Attribute"
  • Choose Transform - Extract - Text Before Delimiter - Delimiter "-", the last delimiter from the end
  • Choose Add Column - Add Index Column
  • Select column "Index"
  • Choose Transform - Standard - Integer-Divide, value 3
  • Select column "Attribute"
  • Choose Transform - Pivot Column - Values column "Value" - advanced option Don't Aggregate
  • Remove column Index

Generated code:

 

let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)

@MarcelBeug

Thank you very much Marcel for this interesting walkthrough, I have applied the steps but I feel there is something not correct with the alignment.

If I am not mistaken, I haven't seen we have used the index and index/3 columns in the step that follow..

 

Capture.PNG

 

Here is the generated code. 

 

Capture2.PNG

 

Thank you again,

@MarcelBeug

 

Hi Marcel, 

 

Sorry for bothering again, but were you able to have a look at the below? 

 

Thanks again

Apologies for the late reaction.

 

You made one mistake: you added a column with the Integer-Divide, you should have Integer-Divided the Index column on the "Transform Column" tab, thus transforming the Index column in the Integer-Divided column.

 

If you corect that, it should be OK.

Specializing in Power Query Formula Language (M)

On second thought, it may not work as I think you have null values.

 

An alternative approach is to unpivot the other columns and split the Attribute column on "-".

Then you can pivot back on column "Attribute,1"  and remove "Attribute,2" afterwards,

 

let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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