Transformming table columns

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.

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!

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?

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!