Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am new to PowerQuery and not a technical person. However, I am using PowerBI for some analysis. I have loaded data in tables and used basic joins to merge and get the final dataset. Everything works fine so far. Now, in the final dataset, I have an identifier ex - Unique ID and then there is a column which has values such as - School or Office. So, there may be either one or two rows for a unique ID as below:
Unique ID Location
---------------------------
1111 Office
2222 School
3333 Office
3333 School
Now, I want to have a new column (Say 'Type) which reads the unique ID columns and assigns values such as - Office, or School , or Both (if it has two rows). So, in above table ID - 1111 will be Office, 2222 will be School, and 3333 will be Both
Solved! Go to Solution.
Hi @sidtembe,
Result
let
Source = #table(type table[Unique ID=Int64.Type, Location=text], {{1111, "Office"}, {2222, "School"}, {3333, "Office"}, {3333, "School"}}),
GroupedRows = Table.Group(Source, {"Unique ID"}, {{"All", each Table.AddColumn(_, "Type", (x)=> if List.Count(List.Distinct([Location])) > 1 then "Both" else x[Location], type text), type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll
Hi @sidtembe,
Result
let
Source = #table(type table[Unique ID=Int64.Type, Location=text], {{1111, "Office"}, {2222, "School"}, {3333, "Office"}, {3333, "School"}}),
GroupedRows = Table.Group(Source, {"Unique ID"}, {{"All", each Table.AddColumn(_, "Type", (x)=> if List.Count(List.Distinct([Location])) > 1 then "Both" else x[Location], type text), type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll