Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JDBOS
Helper III
Helper III

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
amitchandak
Super User
Super User

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

Ashish_Mathur
Super User
Super User

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

v-alq-msft
Community Support
Community Support

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
v-alq-msft
Community Support
Community Support

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.

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
Super User
Super User

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/

@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
Super User
Super User

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.