Home Forums Power Pivot Trouble aggregating single column with multiple values

This topic contains 3 replies, has 2 voices, and was last updated by  Steve Wheeler 4 years, 6 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #6276

    bdeuce14
    Participant
    • Started: 11
    • Replies: 14
    • Total: 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

     

     

    #6279

    Steve Wheeler
    Participant
    • Started: 0
    • Replies: 3
    • Total: 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.

    #6298

    bdeuce14
    Participant
    • Started: 11
    • Replies: 14
    • Total: 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.
    #6300

    Steve Wheeler
    Participant
    • Started: 0
    • Replies: 3
    • Total: 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!

     

     

Viewing 4 posts - 1 through 4 (of 4 total)

The forum ‘Power Pivot’ is closed to new topics and replies.