I have a data set which identifies separate tables through a column Col1 where a %T row value indicates this is a new table (and the name is provided at Col2), and %R indicates this is a record within the table (and the value is provided at Col2).

A data set with two tables would look like this:

I want to be able to separate these tables. My plan is to calculate a new column where the table name would appear, then I can Group By this column and get All, which should return 2 subsets which I can then work with separately.

I am stuck at this step where I need to propagate the table name in all rows for which Col1 = "%R". Ideally, I would add a step named Added Custom Column with the following formula:

= Table.AddColumn(#"Added Index", "TableName", each if [Col1] = "%T" then [Col2] else #"Added Custom Column"[TableName]{[Index]-1})

But this does not work because #"Added Custom Column" does not yet exist, and I understand this would be equivalent to generating some sort of circular reference.

In regular Excel I would insert a column before Col1 and do =if(RC[1]="%T",RC[2],R[-1]C). How can I achieve this with Power Query?

CodePudding user response：

As per my comment, given your data:

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Col1] = "%T" then [Col2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {
        {"all", each _, type table [Col1=nullable text, Col2=nullable text, Custom=text]}
        })
in
    #"Grouped Rows"

Will => the two grouped tables

If you don't want that first row (the one with the table name) to be in the subgroup table, merely change the aggregation to remove it:

{"all", each Table.RemoveFirstN(_,1), type table [Col1=nullable text, Col2=nullable text, Custom=text]}