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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
naga449
Frequent Visitor

How Denormalize piped and comma saperated values in power bi

Hi Community,

 

After playing with Power Query, I can officially say that I'm stuck. 

 

I have source data as shown below.

naga449_0-1680614313275.png

 

the requirement is to denormalize the above data to be denormalized as shown below naga449_1-1680614865901.png

 

Appreciated for the Help,Thank You.

1 ACCEPTED SOLUTION
Pragati11
Super User
Super User

Hi @naga449 ,

 

So you can do it something like shown in steps below.

Consider the data as follows:

Pragati11_0-1680616076671.png

Then I created a custom column in Power Query editor, to get the split of both Rank and Grade columns in a single step as a Table:

Pragati11_1-1680616175425.png

Formula for custom columns:

Table.FromColumns(
    {
        Text.Split([Rank], "|"),
        Text.Split([Grade], "|")
    },
    {"Rank", "Grad"}
)

Once this is created a column is created which can be expanded to extract the final split on columns:

Pragati11_2-1680616264452.png

Click on the Expand icon as highlighted in above picture and you see the columns:

Pragati11_3-1680616339604.png

Once you expand them you get the resultant columns:

Pragati11_4-1680616412653.png

You can simple rename the new columns and delete the previous ones and you get the desired results:

Pragati11_5-1680616497882.png

Hope the solution helps.

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

1 REPLY 1
Pragati11
Super User
Super User

Hi @naga449 ,

 

So you can do it something like shown in steps below.

Consider the data as follows:

Pragati11_0-1680616076671.png

Then I created a custom column in Power Query editor, to get the split of both Rank and Grade columns in a single step as a Table:

Pragati11_1-1680616175425.png

Formula for custom columns:

Table.FromColumns(
    {
        Text.Split([Rank], "|"),
        Text.Split([Grade], "|")
    },
    {"Rank", "Grad"}
)

Once this is created a column is created which can be expanded to extract the final split on columns:

Pragati11_2-1680616264452.png

Click on the Expand icon as highlighted in above picture and you see the columns:

Pragati11_3-1680616339604.png

Once you expand them you get the resultant columns:

Pragati11_4-1680616412653.png

You can simple rename the new columns and delete the previous ones and you get the desired results:

Pragati11_5-1680616497882.png

Hope the solution helps.

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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