Transformming table columns

[gowtham1991]

Hello Experts, 

I have an input table like the below

Itemid	projectid	name	ML0	ML1	ML2	ML3	ML4	ML5	ML6	ML7
123		name1	release2,release1,release 3		release4,release5,release6,release7		release8

and the expected output table to be displayed in power bi is

Itemid	projectid	name	release1	release2	release3	release4	release5	release6	release7	release8
123		name1	ML0	ML0	ML0	ML2	ML2	ML2	ML2	ML5

I would seek your support or suggestion in how to get this done.

Thanks

Gowtham

[SamWiseOwl · Accepted solution]

Hi @gowtham1991 

You can use Transpose to turn the rows into columns.
Then use split column by delimiter to seperate the releases.

Remove the blanks.

Transpose back and promote to headers.

[Sergii24 · Accepted solution]

Hi @gowtham1991, you can achieve the required result by leveraging Pivot and Unpivot functions. Make sure that the column with releases contains only unique values, because column names in Power Query can't repeat (otherwise you'll need to make them unique using index funciton).

Here is a complete code for Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzc1MUdJRKijKz0pNLgGz8xJzU4GUr48BmDQEk0Zg0hhMmoBJUzBpBibNlWJ1opUMjUDyUBNA2opSc1ITi1ONdKAMQxhDAaoQyjWBiZvCGGY6ClCWOYpKCwgPhGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Itemid", "projectid", "name"}, "Attribute", "Value"),
    // Change 10 to the maximum expected value of values to split. If not available, use number much larger from the expected maximum
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10"}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Itemid", "projectid", "name", "Attribute"}, "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns1",{"Attribute.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Value] <> null and [Value] <> ""),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", #"Filtered Rows"[Value], "Value", "Attribute")
in 
    #"Pivoted Column"

Input:

Output:

References where you can find more details on the proposed approach:

Solved: Split comma delimited cell into multiple rows, kee... - Microsoft Fabric Community

excel - How can I transpose only some columns of a table in power query? - Stack Overflow

Table.Pivot - Table Function | Power Query M

Good luck with your project!

[gowtham1991 · Accepted solution]

Hello @SamWiseOwl  , I have to do this for multiple rows. My data set has multiple rows to be converted, and I could achieve this in re ordeing step, as the columns are not fixed, and very dynamic .can you suggest on work around?

[v-venuppu]

Hi @gowtham1991 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

Regards,

Rama U.

[v-venuppu]

Hi @gowtham1991 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Regards,

Rama U.

[v-venuppu]

Hi @gowtham1991 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Regards,

Rama U.

[Sergii24 · Accepted solution]

Hi @gowtham1991, you can achieve the required result by leveraging Pivot and Unpivot functions. Make sure that the column with releases contains only unique values, because column names in Power Query can't repeat (otherwise you'll need to make them unique using index funciton).

Here is a complete code for Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzc1MUdJRKijKz0pNLgGz8xJzU4GUr48BmDQEk0Zg0hhMmoBJUzBpBibNlWJ1opUMjUDyUBNA2opSc1ITi1ONdKAMQxhDAaoQyjWBiZvCGGY6ClCWOYpKCwgPhGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Itemid", "projectid", "name"}, "Attribute", "Value"),
    // Change 10 to the maximum expected value of values to split. If not available, use number much larger from the expected maximum
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10"}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Itemid", "projectid", "name", "Attribute"}, "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns1",{"Attribute.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Value] <> null and [Value] <> ""),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", #"Filtered Rows"[Value], "Value", "Attribute")
in 
    #"Pivoted Column"

Input:

Output:

References where you can find more details on the proposed approach:

Solved: Split comma delimited cell into multiple rows, kee... - Microsoft Fabric Community

excel - How can I transpose only some columns of a table in power query? - Stack Overflow

Table.Pivot - Table Function | Power Query M

Good luck with your project!

[gowtham1991]

Hello @Sergii24 , I have to do this for multiple rows. My data set has multiple rows to be converted, and I could achieve this.can you suggest on work around?

[Sergii24 · Accepted solution]

Hi @gowtham1991, the key to your issue is using transpose/pivot or unpivot functions.

I hope the answers you've got here so far have inspired you so that you can apply them to your specific case. However, the aim of this community is not to solve your problems but rather to guide you in the right direction and inspire you to learn more and resolve them on your own 🙂

Good luck with your project!

[SamWiseOwl · Accepted solution]

Hi @gowtham1991 

You can use Transpose to turn the rows into columns.
Then use split column by delimiter to seperate the releases.

Remove the blanks.

Transpose back and promote to headers.

[gowtham1991 · Accepted solution]

Hello @SamWiseOwl  , I have to do this for multiple rows. My data set has multiple rows to be converted, and I could achieve this in re ordeing step, as the columns are not fixed, and very dynamic .can you suggest on work around?