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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ThiagoRosa
Frequent Visitor

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 lines to be transformed into a single line as exposed in Situation 2.

 

Situation 1    
IDTypeStartEndInterval
1A01010
2A102010
3A203010
4B405010
5B506010
     
Situation 2    
IDTypeStartEndInterval
1A03030
2B406020
2 ACCEPTED SOLUTIONS

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:

v-kelly-msft_0-1616483478123.png

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:

v-kelly-msft_1-1616485557284.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

Anonymous
Not applicable

13 REPLIES 13
Anonymous
Not applicable

this problem is structurally similar to this other

ThiagoRosa
Frequent Visitor

Thank you @Vera_33 .

 

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    
IDTypeStartEndInterval
1A01010
2A203010
3A304010
4B405010
5B506010
     
Situation 2    
IDTypeStartEndInterval
1A01010
2A204020
3B406020

 

Can you help me?

Anonymous
Not applicable

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

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"

@Anonymous thanks.

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

Anonymous
Not applicable

@ThiagoRosa 

check this solution

Dear @Anonymous thanks.

This solution is perfect.

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:

v-kelly-msft_0-1616483478123.png

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:

v-kelly-msft_1-1616485557284.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Dear @v-kelly-msft thank you.

This solution worked very well.

@ThiagoRosa - I don't undersand your expected results.

 

  1. Why  is ID 3 related to type B? It isn't in the first table.
  2. What happened to ID 4 and 5?

Please explain the logic of how table two comes about.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans  I will try to be more specific.

My original database corresponds to the road sections located in Brazil. Highways are named with the prefix "BR" associated with a corresponding number such as "101", for example. In addition, they have a beginning and an end. In my example, I assign the highway code as "Type", the start mileage as "start" and the end as "end". The "interval" corresponds to the difference between the "star" and the "end".

The original database is not well organized and I need to organize it so that it can generate maps.

In some situations, like the one described in my original message, I have two continuous excerpts in two rows from my table and, therefore, I need to combine them into a single record.

In the example I used, in situation 1, ID 3 was joined with ID 2 because it was a continuous stretch, that is, the "star" of ID 3 corresponds to the "end" of ID 2. Thus, ID 2 and ID 3 were merged, in Situation 2, with a new ID 2.

Something similar happened with ID 4 and 5. Since these are continuous stretches, they were condensed, in Situation 2, with a new ID 3.

Sincerely

I've played with it and still not sure I understand.

 

If 2 ends at 30, and 3 starts at 30, I get the connection, but since 3 ends at 40 and 4 starts at 40, why isn't that a continuation? Why did you start over with that part?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Vera_33
Resident Rockstar
Resident Rockstar

Hi @ThiagoRosa 

 

You can GROUPBY ID, paste it in Advanced Editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYgMgNoQSsTrRSkZQcbCYEZKEMVQCLGaMJGECZDoBsQlIzBRJwhQqARYzg0nEAgA=", 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}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Start", each List.Min([Start]), type nullable number}, {"End", each List.Max([End]), type nullable number}, {"Interval", each List.Sum([Interval]), type nullable number}})
in
    #"Grouped Rows"

Vera_33_0-1616033694387.png

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors