cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
massotebernoull
New Member

How to get a custom values from a grouped table?

I have a table with cities and data, like this:

 

CITYDATA
City AX
City AY
City A

X

City AY
City BY
City BY
City CY
City CX
City CX
City DY
City DY
City EX
City EX

 

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:

 

CITYDATA
City AX  (has x and y, but prioritise x)
City BY  (has only y)
City CX  (has x and y, but prioritise x)
City DY  (has only y)
City EX

 

I tried to group by city, then used Table.Contains in the data grouped, but I failed.

Can anyone help me, please?

1 ACCEPTED SOLUTION
watkinnc
Responsive Resident
Responsive Resident

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

View solution in original post

3 REPLIES 3
watkinnc
Responsive Resident
Responsive Resident

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

View solution in original post

massotebernoull
New Member

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:

 

Sem título.png

 

 

 

 

 

 

 

 

 

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:

Sem título2.jpg

 

 

 

 

 

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
Super User III
Super User III

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"

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors