Need Help regarding Power Query

[sidtembe]

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

[dufoq3 · Accepted 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