Forum Discussion

Modnar1's avatar
Modnar1
Copper Contributor
Mar 24, 2022

Have data in multiple rows needs to be combined and extra rows deleted

So some orders will have multiple line items and that creates a duplicate of the sales order number, all i need is a total cost of goods sold for the whole order. So in the first highlighted example ...
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 24, 2022

Modnar1 Perhaps Power Query can help. The attached file contains a query that calculates total CoGS by order number. If that's not what you had in mind, please clarify how the end result should look like.

 

 

Modnar1's avatar
Modnar1
Copper Contributor
Mar 25, 2022
so is there a way to save a power query for use later? This is part of a report I need to process on the regular and generally what I do for these is create a spreadsheet with tabs where i just dump the raw data and then a single output page with all the data i need to see
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 25, 2022

    Modnar1 That's exactly where Power Query comes is. You connect to a data source and perform all sorts of transformations. Each of these these are "recorded" in their own step and will be executed every time you press Refresh. In the example I uploaded earlier, I connected to the data that I named "myData". That's what you can see in the very first applied step.

    Source = Excel.CurrentWorkbook(){[Name="myData"]}[Content],

    But you can connect to a table, a CSV file, a web-page and many more sources.

     

    You may remove everything in A1:I17 in Sheet1 and paste new data somewhere else. But the structure must be the same as the original (i.e. the column names must be the same), but you can paste thousands of rows. As long as you name the entire data set "myData", you can Refresh the query I created. Lear more about PQ in the link below.

    https://exceloffthegrid.com/power-query-introduction/ 

Resources