Forum Discussion

vineshparekh's avatar
vineshparekh
Helper I
1 year ago
Solved

Column to rows convert

Hello, I have this data below where I want to convert rows into columns and columns into rows. Please see the expected result below.   Row Data: Source.Name Type Column2 Column4 Column5 ...
Need Help
Show and Tell
Tips and Tricks
  • sevenhills's avatar
    sevenhills
    1 year ago

    Try this:

     

    Assume in your pq the source data is:

     

    Not optimized approach is to combine Column 2, Column 4, Column 5. But it works!

     

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZRdS4RAGIX/yiBdFLjufOg4Xm62WxvbpxbY4oXoGIK7LpPE9u/THQkKzBkL6ublzMU5+D68x/XauK5eAbRNDLE9AY98m1XC2pcve8M0ZoXYVaKe+lXGm2cUXLZz4TcTGLE55E1FktcgfNu1Zr/abLhIi6T8+hiOehacZ+CsyHMu+LaWNmhB13OloJCoBC0e5itwe7/052ACVuHUnx0d/MzBSAqCXcXlPoG5eGrn3dV4MEWalOB4GQYnv4eH2nIr6Hj4R3gIdTqB6Dg8k/YDoui8mac3wX+4HUY8KTy3o8TGnw8lUlCvBYUtxBD5SENofLn6vdqA+qK0y9UT1EuHNWQlHWQrLjdcLi0wKuXSxdNbLl081MGdUOOsXK4/vJ3vyqV9Pt2/x2P40C3clC2O3wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Type = _t, Column2 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Type", type text}, {"Column2", type text}, {"Column4", type text}, {"Column5", type text}}),

    // Let us merge sets as one; There may be faster way too.
    #"Dataset1" = Table.RemoveColumns(#"Changed Type",{"Column4", "Column5"}),
    #"Renamed Columns" = Table.RenameColumns(Dataset1,{{"Column2", "DataValue"}}),
    #"Dataset2" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column5"}),
    #"Renamed Columns1" = Table.RenameColumns(Dataset2,{{"Column4", "DataValue"}}),
    #"Dataset3" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column4"}),
    #"Renamed Columns2" = Table.RenameColumns(Dataset3,{{"Column5", "DataValue"}}),
    #"MergeAllData" = Table.Combine({#"Renamed Columns", #"Renamed Columns1", #"Renamed Columns2"}),

    // transform ...  pivot .. final output
    #"Added Index" = Table.AddIndexColumn(#"MergeAllData", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundDown(([Index]-1)/4)+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.Mod([Index],4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Columns",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Type]), "Type", "DataValue"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column"," ",null,Replacer.ReplaceValue,{"Airport/Code"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [#"Airport/Code"] <> null and [#"Airport/Code"] <> "")
in
    #"Filtered Rows"

     

     

    note that I added as two step to remove indiex, custom.1 columns. you can combine. I separated them for clarity when going through the data steps in PQ editor.

     

    Steps:

     

    Output:

     

    TODO: Keeping columns: You can decide like custom column, other columns - to keep or not to keep; Decide per your needs.

     

    TODO: Last step rename the final columns per your requirements.

    Hope this helps!

sevenhills's avatar
sevenhills
Super User
1 year ago

 

Roughly,

Your data includes file names and four fields as one set and data is presented in three columns. The actual data values are in three columns, which you are aware of. First, I combined all three data columns into one, resulting in columns named Source.Name, Type, and DataValue.

 

Your data row is a set of four fields, each represented as one row in your data.

 

Now, regarding the code I provided: 

  • "Added Index": Taking the merged data from the previous step, I added a row number column using AddIndexColumn in Power Query.
  • "Added Custom": Adding a custom column to create sets of four rows.

 

You can ignore the steps involving #Added Custom1 and #Removed Columns as they were part of testing. If you check these steps you will see only 1, 2, 3, 0 values. This is to make sure my previous step is doing correct sets of 4. 

 

Pivoting by type for data values is the actual trick that converts the set of 4 rows into 4 columns for the entire dataset.

Lastly, I need to remove the empty sets. I achieve this by selecting the first field "Airport/Code" , replacing it with nulls, and then removing the empty rows. If needed, you can optimize certain steps and remove unwanted steps. I did it in step by step approach for you to understand and get the solution first and then consider the optimizing it.😊

 

Hope this explanation is helpful!