Consolidating data from multiple rows and different columns into single rows

[JDBOS]

We have a Power BI table with multiple rows that we'd like to combine into a report with single rows
This is a nonprofit providing case management support for people who need help, especially relevant in these challenging times.

Plus 5 other Document Types (with Descriptions)

The Case Managers need a report on a page or two, that shows a row for each of their 50 cases. 
Each row would include a Last Completed Date and Next due Date for each Document Type (currently rows in the table)

I'm assuming that the "QSA Last" field (column) would be a measure that would be some kind of a DAX "lookup" of Last_Completed_Date where 

Doc_Type = QSA (Qualified Setting Assessment) 

and Case = Case_Number

So many DAX options - not sure which would be best!  Would you concatenate Doc_Type and Case_Number in the source dataset to simplify the "lookup"?

Thanks in advance for your coaching!

[amitchandak · Accepted solution]

One way is pivot: https://radacad.com/pivot-and-unpivot-with-power-bi

Another way is to summarize

new table =
Summarize(Table, Table[Case Number], "CGA LAST", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Last completed Date])
								   , "CGA Next", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Next Due Date])
		)

A new table like above. Add more columns as per need.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works.  You may also download my PBI file from here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sTtU1VNIBMopS0zPLUosUUssSc0oTSzLz84DCBoa6hoa6RgaGlkqxOkjKA0sTczLTMlNTFIpTS0oy89IVEouLU4uLc1PzSiDaDCzRtBnht8UIi3LCthgaQLXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, #"Document Description" = _t, #"Last completed date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", type text}, {"Document Description", type text}, {"Last completed date", type date}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Document Description", Text.Upper, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Document Description", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Document Description", Text.Clean, type text}}),
    #"Added Custom" = Table.AddColumn(#"Cleaned Text", "Next due date", each if [Document Description]="CAREGIVER EVALUATION" then Date.AddYears([Last completed date],2) else Date.AddYears([Last completed date],1)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Case Number", "Document Description"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","CAREGIVER EVALUATION","CGA",Replacer.ReplaceText,{"Document Description"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","QUALIFIED SETTING ASSESSMENT","QSE",Replacer.ReplaceText,{"Document Description"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Document Description", "Attribute"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Value", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hope this helps.

[v-alq-msft · Accepted solution]

Hi, @JDBOS 

Based on your description, you may create measures as below.

CGA Last = 
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
    CONCATENATEX('Table','Table'[Last Completed Date],","),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Case Number] = _casenum&&
        'Table'[Document Description] = "Caregiver Evaluation"
    )
)

CGA Next = 
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
    CONCATENATEX('Table','Table'[Next Due Date],","),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Case Number] = _casenum&&
        'Table'[Document Description] = "Caregiver Evaluation"
    )
)

QSA Last = 
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
    CONCATENATEX('Table','Table'[Last Completed Date],","),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Case Number] = _casenum&&
        'Table'[Document Description] = "Qualified Setting Assessment"
    )
)

QSA Next = 
var _casenum = SELECTEDVALUE('Table'[Case Number])
return
CALCULATE(
    CONCATENATEX('Table','Table'[Next Due Date],","),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Case Number] = _casenum&&
        'Table'[Document Description] = "Caregiver Evaluation"
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JDBOS]

There's so much to learn about DAX!  Between the three recommended solutions, plenty of good options and features to understand. Nice use of a variable in the last solution @v-alq-msft  - along with Calculate, ConcatenateX, and Filter AllSelected.

Plus Sumarize and Maxx+Filter from @amitchandak  along with Pivot.

And Ashish @Ashish_Mathur  provided M Code that works (once I learn more about how to use M Code 😉

Thanks for the timely help! 

[Ashish_Mathur · Accepted solution]

Hi,

This M code works.  You may also download my PBI file from here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sTtU1VNIBMopS0zPLUosUUssSc0oTSzLz84DCBoa6hoa6RgaGlkqxOkjKA0sTczLTMlNTFIpTS0oy89IVEouLU4uLc1PzSiDaDCzRtBnht8UIi3LCthgaQLXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, #"Document Description" = _t, #"Last completed date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", type text}, {"Document Description", type text}, {"Last completed date", type date}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Document Description", Text.Upper, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Document Description", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Document Description", Text.Clean, type text}}),
    #"Added Custom" = Table.AddColumn(#"Cleaned Text", "Next due date", each if [Document Description]="CAREGIVER EVALUATION" then Date.AddYears([Last completed date],2) else Date.AddYears([Last completed date],1)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Case Number", "Document Description"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","CAREGIVER EVALUATION","CGA",Replacer.ReplaceText,{"Document Description"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","QUALIFIED SETTING ASSESSMENT","QSE",Replacer.ReplaceText,{"Document Description"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Document Description", "Attribute"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Value", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hope this helps.

[JDBOS]

@Ashish_Mathur - thx SO MUCH for PBIX! Clearly your solution works - now I just need to learn about M code and how to work with the source files 😊

As a newcomer to the Power BI/M code world, I'm very impressed.  Let me do a bit of background studying and I'll come back to you with questions! 

[amitchandak · Accepted solution]

One way is pivot: https://radacad.com/pivot-and-unpivot-with-power-bi

Another way is to summarize

new table =
Summarize(Table, Table[Case Number], "CGA LAST", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Last completed Date])
								   , "CGA Next", maxx(filter(Table, table[Document Desc] ="CGA"),Table[Next Due Date])
		)

A new table like above. Add more columns as per need.

[JDBOS]

@amitchandak  I clearly need to learn more about Pivot - so thanks for the Radacad link

And, let me do some testing with Summarize and Maxx w Filter and will keep you posted