Trouble aggregating single column with multiple values

[bdeuce14Participant Started: 11Replies: 14Total: 25]

Hi! I’m brand new to the forum and just picked up Rob’s book. I’ve been exhausting all my options with this to no avail so thought I could send this out and hopefully get some help.

I’m having trouble finding a way to aggregate some data that was collected and entered into a column.

I have a list of transactions and users collected procedures that were listed on each account and entered them into a single column. Some accounts only have a single procedure listed while others could have as many as ten and they are separated by a comma.

I have a lookup table that refers to each procedure and I need to find a way to aggregate these. The data is arranged like the table below. Does anyone have any thoughts on a approach I could take?

Account #                 Date                        Procedure
6037147599            7/26/2016               J2357,96372,99211
6036013179            5/9/2016                 J2357
6036318147           6/10/2016               J2357,93672
6037019927          6/10/2016              J2357,96372
6034426151          4/6/2016                J2357
6035148919          5/4/2016                J2357,96372
6035218060         5/12/2016              J2357,96372
6035739605         6/10/2016              J2357,96372
6023559987         3/3/2016                96372,95117,J2357

 

 

[Steve WheelerParticipant Started: 0Replies: 3Total: 3]

Assuming the data above is in an Excel table, you could Split then Unpivot the Procedures with a Power Query such as below:

let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Account #”, Int64.Type}, {” Date”, type date}, {“Procedure”, type text}}),
#”Split Column by Delimiter” = Table.SplitColumn(#”Changed Type”,”Procedure”,Splitter.SplitTextByDelimiter(“,”, QuoteStyle.Csv),{“Procedure.1”, “Procedure.2”, “Procedure.3″}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Procedure.1”, type text}, {“Procedure.2”, Int64.Type}, {“Procedure.3″, type text}}),
#”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Changed Type1″, {“Account #”, ” Date”}, “Attribute”, “Value”),
#”Changed Type2″ = Table.TransformColumnTypes(#”Unpivoted Other Columns”,{{“Value”, type text}})
in
#”Changed Type2″

That leaves a column for Attribute noting Procedure.1 through Procedure.3 but you can remove that if not needed.

[bdeuce14Participant Started: 11Replies: 14Total: 25]

Thank you for the feedback Steve! I tried this approach and it worked well. I also found a great article that mentioned your approach as well as another and I’ve posted it here:

Split and Unpivot Comma-Separated Values

I’m trying now to aggregate the procedure data and I want to be able to sum the transaction amount associated with each Account #, but with the table now splitting and adding additional rows for each procedure it’s summing everything. I’ve attached a sample of the new table.

I’m unsure if I should be using SUMX or CALCULATE. Could I write a CALCULATE measure that only looks at distinct values?

 

Attachments:

You must be logged in to view attached files.

[Steve WheelerParticipant Started: 0Replies: 3Total: 3]

That DataChant post DOES have an elegant and sustainable solution to this problem…!

On SUMming the Transaction Amount by (distinct) Account #, you COULD split out the Procedures to a separate table, but this forum post (sum column only for unique values in other column) seems to have some guidance using a MAXX measure within a SUMX measure.

I’m not sure how you’ll go though if you want to summarise by Account # AND PatID – good luck!

 

 

[Author]

Posts