Forum Discussion
How to get a custom values from a grouped table?
I have a table with cities and data, like this:
| CITY | DATA |
| City A | X |
| City A | Y |
| City A | X |
| City A | Y |
| City B | Y |
| City B | Y |
| City C | Y |
| City C | X |
| City C | X |
| City D | Y |
| City D | Y |
| City E | X |
| City E | X |
I need to get a dimensional table from this table with single cities based mainly on X data. I need to prioritise if a city has X data. Then, if the city doesn't has X data, only Y, show her too. In the end, I need a table like this:
| CITY | DATA |
| City A | X (has x and y, but prioritise x) |
| City B | Y (has only y) |
| City C | X (has x and y, but prioritise x) |
| City D | Y (has only y) |
| City E | X |
I tried to group by city, then used Table.Contains in the data grouped, but I failed.
Can anyone help me, please?
Before grouping by city, you could add a custom column of 1 for if ends with"X" and 0 for if ends with "Y". The when you group, use the sum aggregation for the number column. If the grouped number column is > 0 then "With Partners" else "Prospects".
So before grouping:
WithOrWithout = Table.AddColumn(TableName, "Partners", each if Text.EndsWith([Data], "X") then 1 else 0, type number))
Then group on City, and use Sum to add the Partners column. Then you can add a Status column:
Table.AddColumn(GroupedTable, "Status", each if [Partners] > 0 then "With Partners" else "Prospects")
That should work!
--Nate
3 Replies
Before grouping by city, you could add a custom column of 1 for if ends with"X" and 0 for if ends with "Y". The when you group, use the sum aggregation for the number column. If the grouped number column is > 0 then "With Partners" else "Prospects".
So before grouping:
WithOrWithout = Table.AddColumn(TableName, "Partners", each if Text.EndsWith([Data], "X") then 1 else 0, type number))
Then group on City, and use Sum to add the Partners column. Then you can add a Status column:
Table.AddColumn(GroupedTable, "Status", each if [Partners] > 0 then "With Partners" else "Prospects")
That should work!
--Nate
Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sqVRwVNJRilCK1UHiRqJyccs64eU6Y3IjcHJdUBWjcV1RFUO5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CITY = _t, DATA = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CITY", type text}, {"DATA", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CITY"}, {{"Res", each let aux_ = List.Distinct([DATA]) in if List.Contains(aux_, "X") then "X" else List.Select(aux_, each _ <>"X"){0} }}) in #"Grouped Rows"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi AlB , I really appreciate your help, but I didn't specified the table in a right way. Sorry about that.
My goal is to create a slicer that can show cities that have companies using my product and cities that don't have.
The best way to do this is matching two columns from a factual table: a column of Cities and a column of companies that already use my product and companies that could use it classified as "prospect" and "partner". Like this:
This way, I have to create a dimension table with a column where:
- If the city has partners, I would like to show something like "with partners" or "True";
- And if the city has just prospects, show "without partners yet" or "False"...
To get a table with unique values:
After a grouped by city code and city, it appeared a column with a table in each row and after that I couldn't get the "city with" column. Is Group By the best way to get this table with unique values?
I tried to adapt your script, but it didn't worked. Is there another way to do this?
