Forum Discussion

Frequent Visitor

5 years ago

Solved

Sumarrize rows

Hi. I have a question about how to handle some data in Power Query. In Situation 1 I have the data disaggregated, for example, for Type A I have 3 lines with contiguous intervals, I need these 3...

Tutorial Requests

v-kelly-msft

5 years ago

Hi ThiagoRosa ,

Try below codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYgMgNoQSsTrRSkZQcSOQmDGShDFUAixmgiRhAmQ6wcRMkSRMoRJgMTOYRCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Start = _t, End = _t, Interval = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Start", Int64.Type}, {"End", Int64.Type}, {"Interval", Int64.Type}}),
    Custom1 = Table.TransformRows(
    #"Changed Type",
    each let 
    mid = Table.Max(Table.SelectRows(#"Changed Type",(x)=>x[Type]=[Type]),"ID")[ID],
    res = 
    if [ID]<>mid 
    then let nextrow = Table.SelectRows(#"Changed Type",(y)=>y[ID]=[ID]+1) in 
    if [Start]+[Interval]=List.Sum(nextrow[Start])
    then let tab = Table.SelectRows(#"Changed Type",(z)=>z[ID]=[ID] or z[ID]=[ID]+1) in 
    [ID=[ID],Type=[Type],Start=List.Min(tab[Start]),End=List.Max(tab[End]),Interval=List.Sum(tab[Interval])] 
    else _
    else [ID=null,Type=null,Start=null,End=null,Interval=null]
    in 
    res
  
),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ID", "Type", "Start", "End", "Interval"}, {"ID", "Type", "Start", "End", "Interval"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([ID] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ID"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "ID", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"ID", "Type", "Start", "End", "Interval"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", Int64.Type}, {"Type", type text}, {"Start", Int64.Type}, {"End", Int64.Type}})
in
    #"Changed Type1"

And you will see:

You could also do it in dax,create 4 columns as below:

Column = 
var _nextstart=CALCULATE(MAX('Table (2)'[Start]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])+1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
var _nextend=CALCULATE(MAX('Table (2)'[End]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])+1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
var _previousstart=CALCULATE(MAX('Table (2)'[Start]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])-1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
var _previousend=CALCULATE(MAX('Table (2)'[End]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])-1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
Return
IF(_nextstart<>BLANK()||_nextend<>BLANK(),
IF('Table (2)'[Start]=_nextstart-'Table (2)'[Interval]&&'Table (2)'[End]=_nextend-'Table (2)'[Interval],1,0),
IF(_nextstart=BLANK()||_nextend=BLANK(),
IF('Table (2)'[Start]=_previousstart+'Table (2)'[Interval]&&'Table (2)'[End]=_previousend+'Table (2)'[Interval],1,0)))

_Start = 
CALCULATE(MIN('Table (2)'[Start]),FILTER('Table (2)','Table (2)'[Column]=EARLIER('Table (2)'[Column])&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))

_End = 
CALCULATE(MAX('Table (2)'[End]),FILTER('Table (2)','Table (2)'[Column]=EARLIER('Table (2)'[Column])&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))

_Interval = CALCULATE(SUM('Table (2)'[Interval]),FILTER('Table (2)','Table (2)'[Column]=EARLIER('Table (2)'[Column])&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Sumarrize rows.pbix39 KB

Anonymous
5 years ago
ThiagoRosa

check this solution
summarize rows (with sub-grouping).pbix23 KB

ThiagoRosa

Frequent Visitor

5 years ago

Thank you Anonymous .

The proposed solution is a good one. However, I checked another situation in my database that prevents it from being applied. There are data of the same type but discontinuous.

Below I present the new situation.

Situation 1
ID	Type	Start	End	Interval
1	A	0	10	10
2	A	20	30	10
3	A	30	40	10
4	B	40	50	10
5	B	50	60	10

Situation 2
ID	Type	Start	End	Interval
1	A	0	10	10
2	A	20	40	20
3	B	40	60	20

Can you help me?

Anonymous

Not applicable

5 years ago

try this.

You have only to add a column interval ([End]-[Start])

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc67DcAwCEXRXahdGH+VMlkDsf8aBvIsUWChewojQkyFXptqw3i0CDX05q0n6IBoI8Gw9bttJpiAaCvBAmxvT/1/c9iAaHxPUD0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Start = _t, End = _t, Interval = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Start", Int64.Type}, {"End", Int64.Type}, {"Interval", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Type", "Start", "End"}, {{"all", each [Start=List.First([Start]),End=List.Last([End])]}}, GroupKind.Local,(x,y)=>Number.From(x[Type]<>y[Type]or y[Start]-x[End]<>0)),
    #"Rimosse colonne" = Table.RemoveColumns(#"Raggruppate righe",{"Start", "End"}),
    #"Tabella all espansa" = Table.ExpandRecordColumn(#"Rimosse colonne", "all", {"Start", "End"})
in
    #"Tabella all espansa"

Anonymous

Not applicable

5 years ago

this should be complete if I understand what you are looking for

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc67DcAwCEXRXahdGH+VMlkDsf8aBvIsUWChewojQkyFXptqw3i0CDX05q0n6IBoI8Gw9bttJpiAaCvBAmxvT/1/c9iAaHxPUD0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Start = _t, End = _t, Interval = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Start", Int64.Type}, {"End", Int64.Type}, {"Interval", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Type", "Start", "End"}, {{"all", each [Start=List.First([Start]),End=List.Last([End]), Interval=List.Sum([Interval])]}}, GroupKind.Local,(x,y)=>Number.From(x[Type]<>y[Type]or y[Start]-x[End]<>0)),
    #"Rimosse colonne" = Table.RemoveColumns(#"Raggruppate righe",{"Start", "End"}),
    #"Tabella all espansa" = Table.ExpandRecordColumn(#"Rimosse colonne", "all", {"Start", "End","Interval"})
in
    #"Tabella all espansa"

ThiagoRosa

Frequent Visitor

5 years ago

Anonymous thanks.

I will try this solution. I will give feedback later.

v-kelly-msft

Community Support

5 years ago

Hi ThiagoRosa ,

Try below codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYgMgNoQSsTrRSkZQcSOQmDGShDFUAixmgiRhAmQ6wcRMkSRMoRJgMTOYRCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Start = _t, End = _t, Interval = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Start", Int64.Type}, {"End", Int64.Type}, {"Interval", Int64.Type}}),
    Custom1 = Table.TransformRows(
    #"Changed Type",
    each let 
    mid = Table.Max(Table.SelectRows(#"Changed Type",(x)=>x[Type]=[Type]),"ID")[ID],
    res = 
    if [ID]<>mid 
    then let nextrow = Table.SelectRows(#"Changed Type",(y)=>y[ID]=[ID]+1) in 
    if [Start]+[Interval]=List.Sum(nextrow[Start])
    then let tab = Table.SelectRows(#"Changed Type",(z)=>z[ID]=[ID] or z[ID]=[ID]+1) in 
    [ID=[ID],Type=[Type],Start=List.Min(tab[Start]),End=List.Max(tab[End]),Interval=List.Sum(tab[Interval])] 
    else _
    else [ID=null,Type=null,Start=null,End=null,Interval=null]
    in 
    res
  
),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ID", "Type", "Start", "End", "Interval"}, {"ID", "Type", "Start", "End", "Interval"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([ID] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ID"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "ID", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"ID", "Type", "Start", "End", "Interval"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", Int64.Type}, {"Type", type text}, {"Start", Int64.Type}, {"End", Int64.Type}})
in
    #"Changed Type1"

And you will see:

You could also do it in dax,create 4 columns as below:

Column = 
var _nextstart=CALCULATE(MAX('Table (2)'[Start]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])+1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
var _nextend=CALCULATE(MAX('Table (2)'[End]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])+1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
var _previousstart=CALCULATE(MAX('Table (2)'[Start]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])-1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
var _previousend=CALCULATE(MAX('Table (2)'[End]),FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])-1&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))
Return
IF(_nextstart<>BLANK()||_nextend<>BLANK(),
IF('Table (2)'[Start]=_nextstart-'Table (2)'[Interval]&&'Table (2)'[End]=_nextend-'Table (2)'[Interval],1,0),
IF(_nextstart=BLANK()||_nextend=BLANK(),
IF('Table (2)'[Start]=_previousstart+'Table (2)'[Interval]&&'Table (2)'[End]=_previousend+'Table (2)'[Interval],1,0)))

_Start = 
CALCULATE(MIN('Table (2)'[Start]),FILTER('Table (2)','Table (2)'[Column]=EARLIER('Table (2)'[Column])&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))

_End = 
CALCULATE(MAX('Table (2)'[End]),FILTER('Table (2)','Table (2)'[Column]=EARLIER('Table (2)'[Column])&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))

_Interval = CALCULATE(SUM('Table (2)'[Interval]),FILTER('Table (2)','Table (2)'[Column]=EARLIER('Table (2)'[Column])&&'Table (2)'[Type]=EARLIER('Table (2)'[Type])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Sumarrize rows.pbix39 KB

Forum Discussion

Sumarrize rows

Related content

Sumarrize rows

Sumarrizing problem

remove duplicate value within a row

Matrix Row Conditional Formatting

Where is the Multi-row card