Table's transposing

[Ritaf]

Hi all,

I need some help on the issue of tables transformations with PQ.

I have a horizontal table and have to transform it to vertical 
like in the picture.

I spent a few hours on try to do it without success.
Here is my PBIX with the original table and the table with my trial manipulations,
hope somebody can help me with this:
https://drive.google.com/drive/folders/180w1__xEBq31goTDTM_gOOK_Y7MUzHkq

[CNENFRNL · Accepted solution]

You can finish the trasformation by just clicking UI,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVLLDoIwEPwVw5kmIJKUgyeMF4IhXgmHAgvS8DA8Dv692154KqBkszudbieztL6vKKqStVBkMQLbI/rRQDAMSk39dieOdZ5sHLCdUk2b0A7Hb9IcqL5id01bFVDj2qsrDlHrspKlkuCsxJxAiLlggmHPWuLXIsO7UuZ8dfcj36XSFRN2oJUuMIg5GoccZ+N9CyEYjgV/DKEUbVMiFl3m9eEsQlBcsVs10Cw0071zAoILKzPItxwh01fVe9Rk1bAK2WQuSwxzj7mRyxTBtYb478t49ELC7EpZ+RmnxRQEbw==", 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, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Merged Columns1" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {";itemid;Customer;ProjectManager"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Value] <> "")),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows", ";itemid;Customer;ProjectManager", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {";itemid;Customer;ProjectManager.1", ";itemid;Customer;ProjectManager.2"})
in
    #"Split Column by Delimiter1"

[Ritaf]

Wow! It's worked like a magic,
I'll try it on my real table

[BA_Pete]

Hi @Ritaf ,

The PBIX you supplied is still trying to connect to your local drive, so can't be viewed in Power Query.

Can you copy the original table, paste it into 'Enter Data' in PQ, then copy the M code from Advanced Editor and paste it into a code window here please ( </> button) ?

Pete

[Ritaf]

Thank you for your response.
I updated the pbix and put excel with the tables too to the linked folder.
Hope it will help 🙂