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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sahildesai360
New Member

Adding rows together

If I wanted to add rows 2 and 3 to row one how would I do? Rows with value containing "ERC" with securityclass01 value being "ERC" need to be rollup together. I don't want to lose any of the other data the way it is. 

sahildesai360_0-1673476779125.png

 

1 REPLY 1
edhans
Super User
Super User

Try this.

edhans_0-1673477500317.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJLEnLL8pV0lFyzkksLgbSYYk5palKsTrRSq5BzkA+hDSEiSQWw8WMYGLBrm5wQWOwYCKQlVickgakTMACSQgBU7BAMkLADCyQghAwV4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value", Int64.Type}}),
    ERCFilter = Table.SelectRows(#"Changed Type", each Text.Contains([Platform], "ERC") and [Class] = "ERC"),
    ERCGroup = Table.Group(ERCFilter, {"Class"}, {{"Platform", each List.Min([Platform]), type nullable text}, {"Value", each List.Sum([Value]), type nullable number}}),
    OtherFilter = Table.SelectRows(#"Changed Type", each not Text.Contains([Class], "ERC")),
    #"Appended Query1" = Table.Combine({OtherFilter, ERCGroup})
in
    #"Appended Query1"

 

This is the logic:

  1. Filter the data you want to group by
  2. Create your group - this is the ERCGroup line
  3. Create a step and filter the original table to exclude what you want - the original table is the #"Changed Type" step.
  4. Append the query to itself, then change the Table.Combine function to combine ERCGroup and OtherFilter.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 

 

If you need more help, please provide valid data. I cannot paste an image in to Power Query, and I'm not typing all of that. 😁
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors