Unstacking a column with variable intervals of data

[althorian]

Hi,

I have a power query problem I can't solve. I have data sourced a text file in a single column. In the data there are interface records that may or may not have related data, if there is related data it follows in the next lines until there is another interface record. I need to get each of the 'Word' records on their own columns in rows next to their corresponding Interface record.

I have an example below of what the source data structure looks like and further down how it should look.

Interface.Name1

Word1.Variable

Word2.Variable

Interface.Name2

Interface.Name3

Word1.Variable

Word3.Variable

Interface.Name4

Word2.Variable

Interface.Name5

Word1.Variable

Word2.Variable

Word3.Variable

<tbody>
</tbody>

Interface.Name1	Word1.Variable	Word2.Variable
Interface.Name2
Interface.Name3	Word1.Variable		Word3.Variable
Interface.Name4		Word2.Variable
Interface.Name5	Word1.Variable	Word2.Variable	Word3.Variable

<tbody>
</tbody>

I am having a hard time establishing a relationship between each 'set' of records. I have tried adding new columns for each type of record but when indexing but it doesn't work with the variable length between each record 'set'

Thanks
Ryan

 

[billszysz]

Hi althorian,
Try this code below

let
    Source = Excel.CurrentWorkbook(){[Name="RawDataTbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Interface", each if Text.StartsWith([Data], "Interface") then [Data] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Interface"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Data], "Interface")),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Data", "Data — kopia"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Data — kopia"]), "Data — kopia", "Data")
in
    #"Pivoted Column"

where "RawDataTbl" is a one column table to transform with header "Data".

Regards

 

[althorian]

Thank you this works great!

 

[Useful]

Hello,

I've made a video lesson about this. See here (also you may download example file)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   //1) Using add conditional column feature to define left side headers (row headers)
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([Column1], "Interface.Name") then [Column1] else null ),
   //2) Fill down will help us to groupping
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
   //3) Group by "Custom" column and remove "Custom" column
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Gr", each Table.RemoveColumns(_,"Custom"), type table}}),
   //4) Adding another column to configure the column headers. And these names will be used in Table.Transpose() function
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Cols", each List.ReplaceRange([Gr][Column1],0,1,{"Interface.Name"})),
   //5) Using Table.Transpose() function 
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Gr],[Cols])),
   //6) Removing extra columns
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1"}),
   //7) Drill down the table and using Table.Combine() function
    Finish = Table.Combine(#"Removed Other Columns"[Custom.1])
in
    Finish

 

[althorian]

Thanks for the video! that's a great way to separate out the data. I will definitely use this in the future.

Ryan

 

[Useful]

Thanks for the video! that's a great way to separate out the data. I will definitely use this in the future.

Ryan

Enjoy! Good luck!