CODEWITHSUNDEEP
powerbipowerquery
Joe Crozier
Joe Crozier

Reputation: 1036

Custom grouped column and removing identical rows

I have raw data like this:

enter image description here

There's other columns not shown but disease site is the only one that will change within a study.

And in power Bi, ultimately in the report I want to show a table to colleagues that has just one row per study. Obviously given the unique disease sites, I need to group those first, and I need some help doing so. What I'd like to show colleagues is a table like this:

enter image description here

Where if there were multiple disease sites associated with a "study" then they are clumped as "multi". I figure to do so it'll mean creating a custom disease site column with 'multi' in it and then filter to one row per study, but I'm having trouble with the details.

Do I do that in power query? Should I do it in power bi after the query is imported? Any help would be appreciated, thank you!

Upvotes: 0

Views: 41

Answers (1)

horseyride
horseyride

Reputation: 21318

Load your data into Powerquery or similar

Click select the Study and Primary_Investigatory columns, right click, group by and choose operation All Rows

Change the ending of the group in the formula window (or in Home .. advanced editor) from

{"Primary_Investigatory", "Study"}, {{"data", each _, ...  })

to

{"Primary_Investigatory", "Study"}, {{"data", each if Table.RowCount(_) = 1 then [Disease_Site]{0} else "Multi"}})

sample full code for example image:

 let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary_Investigatory", type text}, {"Study", type text}, {"Disease_Site", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Primary_Investigatory", "Study"}, {{"data", each if Table.RowCount(_) = 1 then [Disease_Site]{0} else "Multi"}})
in  #"Grouped Rows"

enter image description here

Upvotes: 2

Related Questions