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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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