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
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