Forum Replies Created
-
Posts
-
Try
Denial Transactions for JCodes:=Calculate([Denial Transactions], LEFT([CPT Code],1)=”J”)
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!
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.
