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.

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.

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.