Power Query -- Fill down based on grouped index

[gxm204]

Hi all, This is in some ways a follow-up to an earlier thread. I was able to create a grouped index number to repeat at every N rows (which effectively partitions my groups), but now I am trying to figure out how to create a column where the first entry in the `Location` column of each `Group` index is used to fill a new column. This way I actually have the grouped labeled by location.

Thoughts and ideas appreciated, thank you.

 

[alansidman]

No one really wants to rebuild your data to find a solution. Post some sample data using XL2BB. Also show a mocked up solution so we understand exactly what you are looking to do.

 

[gxm204]

Hi all, This is in some ways a follow-up to an earlier thread. I was able to create a grouped index number to repeat at every N rows (which effectively partitions my groups), but now I am trying to figure out how to create a column where the first entry in the `Location` column of each `Group` index is used to fill a new column. This way I actually have the grouped labeled by location.

Thoughts and ideas appreciated, thank you.

View attachment 35831

OK, here is the XL2bb: The Location-Fill is the mocked-up column that I am hoping to derive based on the Location and Group columns.

Location	Group	Location-Fill (derive this column)
Mid Michigan	0	Mid Michigan
Clicks	0	Mid Michigan
Impressions	0	Mid Michigan
South Michigan	1	South Michigan
Clicks	1	South Michigan
Impressions	1	South Michigan
West Michigan	2	West Michigan
Clicks	2	West Michigan
Impressions	2	West Michigan

 

[XL Pro]

Will this work for you:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Group", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Custom", each _, type table [Location=nullable text, Group=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Locations-Fill", each [Custom]{0}[Location]),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Location"}, {"Location"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Location", "Group", "Locations-Fill"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", type text}, {"Group", Int64.Type}, {"Locations-Fill", type text}})
in
    #"Changed Type1"

 

[alansidman]

Here is another Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Group", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Data", each _, type table [Location=nullable text, Group=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Location", "Index"}, {"Custom.Location", "Custom.Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Custom.Index] =1 then [Custom.Location] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Data", "Custom.Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.Location", "Group", "Custom"})
in
    #"Reordered Columns"