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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Interface.Name1[/TD]
[/TR]
[TR]
[TD]Word1.Variable[/TD]
[/TR]
[TR]
[TD]Word2.Variable[/TD]
[/TR]
[TR]
[TD]Interface.Name2[/TD]
[/TR]
[TR]
[TD]Interface.Name3[/TD]
[/TR]
[TR]
[TD]Word1.Variable[/TD]
[/TR]
[TR]
[TD]Word3.Variable[/TD]
[/TR]
[TR]
[TD]Interface.Name4[/TD]
[/TR]
[TR]
[TD]Word2.Variable[/TD]
[/TR]
[TR]
[TD]Interface.Name5[/TD]
[/TR]
[TR]
[TD]Word1.Variable[/TD]
[/TR]
[TR]
[TD]Word2.Variable[/TD]
[/TR]
[TR]
[TD]Word3.Variable[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Interface.Name1[/TD]
[TD]Word1.Variable[/TD]
[TD]Word2.Variable[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Interface.Name2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Interface.Name3[/TD]
[TD]Word1.Variable[/TD]
[TD][/TD]
[TD]Word3.Variable[/TD]
[/TR]
[TR]
[TD]Interface.Name4[/TD]
[TD][/TD]
[TD]Word2.Variable[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Interface.Name5[/TD]
[TD]Word1.Variable[/TD]
[TD]Word2.Variable[/TD]
[TD]Word3.Variable[/TD]
[/TR]
</tbody>[/TABLE]

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!