Forum Discussion

Ritaf's avatar
Ritaf
Responsive Resident
4 years ago
Solved

Table's transposing

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

Need Help
Show and Tell
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    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"

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

    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's avatar
      Ritaf
      Responsive Resident
      4 years ago

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

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    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's avatar
      Ritaf
      Responsive Resident
      4 years ago

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