How to get a custom values from a grouped table?

[massotebernoull]

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?

[watkinnc · Accepted solution]

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

[massotebernoull]

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?

[AlB]

Hi @massotebernoull 

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.