Search code examples
groupingdaxpowerquery

Power Query Transform data within Group/Buckets

I want to transform a column in Power query. Only the transformation should be applied within the group based on a condition. This is my data.

enter image description here

Here in the above table, I just want to transform Office based column to all 1 if any Office-based column is set to 1 on the particular ID group. But all the Office based column value is 0 on the particular ID group, it should not transform the column.

My expected result would be,

enter image description here It would be fine, If an additional column can have the transformed column.

Solution

  • try this

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Item", type text}, {"Home Based", Int64.Type}, {"Office Based", Int64.Type}, {"Amount", Int64.Type}}),

// find all IDs with 1 in Office Based
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Office Based] = 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ID"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

//merge that back in
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Removed Duplicates", {"ID"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"ID"}, {"ID.1"}),

// if there was a match convert to 1 otherwise take original number
#"Added Custom" = Table.AddColumn(#"Expanded Table2", "OfficeBased2", each try if Text.Length([ID.1])>0 then 1 else [Office Based] otherwise [Office Based]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Office Based", "ID.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"OfficeBased2", "OfficeBased"}})
in #"Renamed Columns"

    or the more compact version:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source,"Custom",(i)=>Table.SelectRows(Source, each [ID]=i[ID]) [Office Based]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Office Based2", each if List.Contains([Custom],1) then 1 else [Office Based]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Office Based"})
in  #"Removed Columns"

    The first method probably works best for large data sets