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
mahender
Frequent Visitor

split multiple column values into multiple rows

Hi,

 

I'm hoping I can get some help on this as I've exhausted my limited knowledge and research abilities on this one. 

I have data in below format (1st image) where some columns are repeated for each record (like status, year, month, amount values). I am trying to structure data into below image (2nd image) where one row for each status, year, month, amount values.

 

Any help to transform the data into below required format is appreciated.

 

Input dataInput data

Expected OutputExpected Output

 

Regards,

Mahender

1 ACCEPTED SOLUTION

A more dynamic solution in Power Query, that allows for additional groups of columns, is:

1. Unpivot all columns except Record ID.

2. Get rid of the sequence numbers in the "Attribute" column (former column names).

3. Add a temporary Index column (from 0) and integer-divide this by 4 (the number of fields in each group),
    so you get 0,0,0,0,1,1,1,1,2,2,2,2 etcetera.

4. Pivot the "Attribute" column with advanced option "Don't Aggregate".

5. Remove the temporary Index column.

 

Code below and this video takes you through the steps (the video starts at a "Changed Type" step which I removed after the recording as this would jeopardize the dynamics of the solution).

At the end of the code you may want to add a "Change Type" step.

 

The steps are all created via menu options in Power Query, but I adjusted the "Extracted First Characters" code to extract all characters up to the first digit (0-9).

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Record ID"}, "Attribute", "Value"),
    #"Extracted First Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, Text.PositionOfAny(_,Text.ToList("0123456789"))), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted First Characters", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), 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)

View solution in original post

10 REPLIES 10
SinTej123
Regular Visitor

Hi All,

 

I have one requirement to compare the User name data (delimited with ';') loaded in SSAS tabular model column, with the user name from power BI. May I know what all are the possible ways to resolve this?

 

Earlier help is much more appreciated!!

GilbertQ
Super User
Super User

Hi @mahender

 

If you were looking to do this using the Query Editor I would do it in the following way:

 

  • Create a new table  called "Table 1" which has the following columns:
    • Record ID, Status1, Year1, Month1, Amount1
  • I would then rename the columns in "Table 1" to be:
    • Record ID, Status, Year, Month, Amount
  • Next I would create another table called "Table 2" which would contain the following columns:
    • Record ID, Status2, Year2, Month2, Amount2
  • I would then rename the columns in "Table 2" to be:
    • Record ID, Status, Year, Month, Amount
  • Next I would use the Append Queries as New.
    • I would then Append "Table 1" and "Table 2"
    • NOTE: What happens here is that where the column names are the same, it will put the values in the same columns.
  • Finally I would disable the loading of "Table 1" and "Table 2" into your Power BI Model, because they are no longer required, by right clicking on "Table 1" and "Table 2" and de-selecting "Enable Load"

That will then import into your Power BI Model, as you want below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

A more dynamic solution in Power Query, that allows for additional groups of columns, is:

1. Unpivot all columns except Record ID.

2. Get rid of the sequence numbers in the "Attribute" column (former column names).

3. Add a temporary Index column (from 0) and integer-divide this by 4 (the number of fields in each group),
    so you get 0,0,0,0,1,1,1,1,2,2,2,2 etcetera.

4. Pivot the "Attribute" column with advanced option "Don't Aggregate".

5. Remove the temporary Index column.

 

Code below and this video takes you through the steps (the video starts at a "Changed Type" step which I removed after the recording as this would jeopardize the dynamics of the solution).

At the end of the code you may want to add a "Change Type" step.

 

The steps are all created via menu options in Power Query, but I adjusted the "Extracted First Characters" code to extract all characters up to the first digit (0-9).

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Record ID"}, "Attribute", "Value"),
    #"Extracted First Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, Text.PositionOfAny(_,Text.ToList("0123456789"))), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted First Characters", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), 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)

The video is timeless and priceless. 14 seconds in and my variation of the problem is solved! I salute you, sir.

Dear Marcel! Thank you very much for the solution! The solution works like magic. But recently, I am having trouble when Pivoting the column as specified in last step. I have  approx 37000 rows and 22 Columns (2 fields in each group, 11 groups).  Power query returns the error as DataFormat.Error: Invalid cell value '#N/A'. I tried everything i could in excel sheet, ranging from removing the #N/A errors to deleting the less relevant columns. Still the error is persistent. Any help would be apreciated! 

Thanks.

S

Apparently you have #N/A errors in your source data, which had nothing to do with this topic, so my suggestion for you is to raise your own topic.

 

You should be aware that a forum is for everybody to help you and not try and claim one person (i.c. me) as a personal assistent to help you free of charge.

Specializing in Power Query Formula Language (M)

Dear Marcel! Thank you very much for the solution! The solution works like magic. But recently, I am having trouble when Pivoting the column as specified in last step. I have  approx 37000 rows and 22 Columns (2 fields in each group, 11 groups).  Power query returns the error as DataFormat.Error: Invalid cell value '#N/A'. I tried everything i could in excel sheet, ranging from removing the #N/A errors to deleting the less relevant columns. Still the error is persistent. Any help would be apreciated! 

Thanks.

S

Thanks for the solution, it worked like charm.

Sean
Community Champion
Community Champion

@MarcelBeug  I just knew you'll come up with a better way as soon as you see this question! Smiley Happy

I've seen Step 3 from your video here and was trying to go exactly the same way HOWEVER I was missing Step 2!

Great job as always! Smiley Happy

Phil_Seamark
Employee
Employee

Hi @mahender

 

You can do it in DAX easily enough by adding the following Calculated Table

 

New Table = UNION(
                SELECTCOLUMNS('Table1',"Report ID",[Report ID],"Status",[Status1],"Year",[Year1],"Month",[Month1],"Amount",[Amount1]),
                SELECTCOLUMNS('Table1',"Report ID",[Report ID],"Status",[Status2],"Year",[Year2],"Month",[Month2],"Amount",[Amount2])
                )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.