cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Consolidating data from multiple rows and different columns into single rows

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.

image.png

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)

image.png

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!

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Consolidating data from multiple rows and different columns into single rows

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User V
Super User V

Re: Consolidating data from multiple rows and different columns into single rows

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Community Support
Community Support

Re: Consolidating data from multiple rows and different columns into single rows

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:

a1.png

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: Consolidating data from multiple rows and different columns into single rows

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User V
Super User V

Re: Consolidating data from multiple rows and different columns into single rows

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper II
Helper II

Re: Consolidating data from multiple rows and different columns into single rows

@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

Highlighted
Helper II
Helper II

Re: Consolidating data from multiple rows and different columns into single rows

@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! 

Highlighted
Community Support
Community Support

Re: Consolidating data from multiple rows and different columns into single rows

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:

a1.png

 

Best Regards

Allan

 

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

View solution in original post

Highlighted
Helper II
Helper II

Re: Consolidating data from multiple rows and different columns into single rows

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! 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors