Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
40 | |
26 | |
22 | |
21 | |
16 |