none
Using Power Query to transform dataset (Strange Format) RRS feed

  • Question

  • I am attempting to transform a report in Power Query and I can not figure out how to accomplish the task. It's a difficult transformation to explain so I'll post pictures of the original formatting and the goal formatting. I've been able to get half way there in Power Query but the second transpose step is giving me trouble. Current Query = Table.Transpose(#"Changed Type"). After this I need to transpose columns 6 through-->End, and that is where I am stuck.

    I can not currently post pictures but the source data is setup as follows

    Columns 1  Column B      Column C

    blank          Project               189

    blank          Plan                    2080

    blank         Plan Name          blank

    blank        Elevation             A

    blank        Square Feet       2080

    CC            CC Desc             blank

    0001        Survey Fees       $89

    Desired Result

    Column 1     Column 2       Column 3

    CC               Cost Code       Cost         Project         Plan          Elevation         SF

    00001        Survey Fees       $89          189              2080         A                     2080


    • Edited by Jae_Arv Friday, July 5, 2019 4:21 PM Can not add pictures, posted example tables
    Friday, July 5, 2019 4:10 PM

Answers

  • This may help you https://exceleratorbi.com.au/pivoting-text-data-using-power-query/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, July 5, 2019 10:37 PM
  • Not sure about the column names of your desired output, but this should get you started:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCijKz0pNLgGyDC0slWJ1III5iXlAysjAwgBZSMEvMTcVyIaJueakliWWZOaD1DrCBIMLSxOLUhXcUlNLkI1wdgbynJ0VXFKLk2FGGBgYGIJ0lBaVpVaCdBQDeSBXxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
        Custom1 = Table.SelectRows(Source, each ([Column1] = "")),
        #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column1"}),
        #"Transposed Table" = Table.Transpose(#"Removed Columns"),
        Custom2 = Table.ToColumns(#"Filtered Rows") & Table.ToColumns(#"Transposed Table"),
        Custom3 = Table.FromColumns(Custom2)
    in
        Custom3


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, July 7, 2019 4:14 AM
    Moderator

All replies

  • This may help you https://exceleratorbi.com.au/pivoting-text-data-using-power-query/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, July 5, 2019 10:37 PM
  • Not sure about the column names of your desired output, but this should get you started:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCijKz0pNLgGyDC0slWJ1III5iXlAysjAwgBZSMEvMTcVyIaJueakliWWZOaD1DrCBIMLSxOLUhXcUlNLkI1wdgbynJ0VXFKLk2FGGBgYGIJ0lBaVpVaCdBQDeSBXxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
        Custom1 = Table.SelectRows(Source, each ([Column1] = "")),
        #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column1"}),
        #"Transposed Table" = Table.Transpose(#"Removed Columns"),
        Custom2 = Table.ToColumns(#"Filtered Rows") & Table.ToColumns(#"Transposed Table"),
        Custom3 = Table.FromColumns(Custom2)
    in
        Custom3


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, July 7, 2019 4:14 AM
    Moderator