CODEWITHSUNDEEP
groupingdaxpowerquery
Smith Dwayne
Smith Dwayne

Reputation: 2807

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.

Upvotes: 0

Views: 606

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60484

Here's another method:

  • Group by ID
  • Apply the Table.TransformColumns operation to each subtable
  • Then re-expand
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Item", type text}, {"Home Based", Int64.Type}, {"Office Based", Int64.Type}, {"Amount", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {        
        {"Xform", (t)=>Table.TransformColumns(t, {
            {"Office Based", each if List.ContainsAny(t[Office Based],{1}) then 1 else 0}}) ,       
        type table 
            [ID=nullable text, Item=nullable text, Home Based=nullable number, Office Based=nullable number, Amount=nullable number]}
            }),
    #"Expanded Xform" = Table.ExpandTableColumn(#"Grouped Rows", "Xform", 
        {"Item", "Home Based", "Office Based", "Amount"}, 
        {"Item", "Home Based", "Office Based", "Amount"})
in
    #"Expanded Xform"

enter image description here

Upvotes: 1

horseyride
horseyride

Reputation: 21428

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

Upvotes: 1

Related Questions