How Denormalize piped and comma saperated values in power bi

[naga449]

Hi Community,

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

I have source data as shown below.

the requirement is to denormalize the above data to be denormalized as shown below 

Appreciated for the Help,Thank You.

[Pragati11 · Accepted solution]

Hi @naga449 ,

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

Consider the data as follows:

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:

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:

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

Once you expand them you get the resultant columns:

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

Hope the solution helps.