Hello.
In the attached file, I left 2 different towns from a very long file with hundreds of towns for the example.
I need to add a calculated column which is calculating the daily number of each row. The result is in fact the number of the precedent row minus the actual row and so pn for a given town code. In SQL, I was running a "run over partition" code. How can I get that with PQ ?
I wrote in Column E the value that should be calculated in red.
Many thanks. Bruno.
Hi Bruno,
Please refer to this tutorial on Grouped Running Totals in Power Query and modify the formula to subtract rather than add.
Mynda
Hi Bruno,
See attached file for solution. Query result is loaded onto Sheet1.
Regards
Phil
Thank you both.
I have to admit this is a mess for a so "simple task" like that, not easy to get at least for the first time. I succeeded to figure out and to work with after few hours, I think it must be done after your entire course I didn't finish yet.
I have a problem with that : I have to run that task for >60000 rows and it is not a one shot work but should be a daily or weekly routine automation. Is there any possibility to get something that runs quicker ?
Bruno
Hi Bruno,
How long it it taking? I've run this query against 100,000 rows and it takes a few seconds. And all you need to do is click on Refresh to run it again?
Can you share the full data?
Phil
Philip, let me try again. If you are running that quickly it seems I am doing something wrong. I'll be back to you here after some "homeworks" .
Philip. This is frustrating but after all the mistakes I did, I guess I do not have many others to do yet ... kind of positive way to look at 🙂
It is far from beeing simple, many thanks for your patience.
Attached is the file. I will have many calculation to perform and I must understand how the code is working.
Again, I try to get the additional number for every day for every town from the cummulative data I have. I copy paste your code in the query in the attached file (Daily_beat) but it is not working. I continue to get an infinite time running when "invoke" it from the query for two towns only (I stopped it after 8 min when it says on the bottom right more than 150 MD for Corona_Data.xlsx), and errors when running it from additional column -> "invoke custom function" (error: Expression.Error: A cyclic reference was encountered during evaluation)
I sent only few towns, the file with all the data is >2.8MB
Bruno
Hi Bruno,
The way you have implemented the function isn't the way I did it in the example file I provided.
Rather than Add Column -> Invoke Custom Function you need to create a step where the function is called and you pass in the 2 columns : the values and the grouping.
The function returns a list which needs to then be merged into the original table.
Before calling the function you also need to create buffered lists - this is important to speed up the whole process.
In these 3 lines you see I've created buffered lists for the 2 input parameters (columns) and then called the function, creating a table from the function output and assigning that the name RT
BufferedValues = List.Buffer(#"Promoted Headers"[Cumulative_verified_cases]),
BufferedGroup = List.Buffer(#"Promoted Headers"[City_Code]),
RT = Table.FromList(Daily_Beat(BufferedValues, BufferedGroup), Splitter.SplitByNothing(), {"GRT"}, null, ExtraValues.Error),
Then get a list of columns that will be recombined into a table
Columns = List.Combine({Table.ToColumns(#"Promoted Headers"),Table.ToColumns(RT)}),
and create the table
#"Converted to Table" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Promoted Headers"),{"GRT"}})),
The query DummySource contains all of these steps and the resulting table is on the Result sheet. The query takes seconds to run using a file on my hard drive.
I recommend that you start with the code from DummySource. Duplicate it and work on the copy so the original stays intact.
I've changed the file path/name in the SourceFile step to match your file location
If you are still having some issues I will need you to attach the source workbook Corona_Data.xlsx so I can work with it.
Regards
Phil
Hello Phil. Still don't work for me, I miss certainly something basic in your explanation. I cannot add the file since it weight 2.8 MB and the blog is allowing only 0.5.
I will continue with the M course in the Power Query syllabus, and will be back to you after that, too pity to waste your time. This is important to me since I will have to play with the data by calculating many times in that way (last week data, last months, last 10 points, bin the data by x points and so on.) I used to work with Jump and need to learn to do it with Excel today...
Bruno
Hi Bruno,
You can put the file onto OneDrive, Google Drive or DropBox then email me a link to download it.
Or email the file to me
Phil
Sent. Thanks.
Nothing received.
Strange. It seems to be sent from my mailbox.
You have it here.
Phil shalom.
This is the link of the file, ignore the names of the towns, it is in Hebrew, you can work with the city_code.
Anyhow, since I want to understand how we calculate the partition over in PQ, let me know if it is OK with you to ask if I do not understand how you manage to get that.
Many thanks, Bruno
Hi Bruno,
That file doesn't have any of my code in it so I can't see what you are doing and fix the problems.
I've already supplied instructions and a sample file in Reply #8 above so please refer back to that and try to implement what I've said. If that does not work then send me the file that is not working.
And I will need the source data file you are using. When I open your file it's pointing to a file on your hard drive that I don't have.
Phil
Phil. I did not succeed to manage. Many small steps are going OK but I cannot understand how I should proceed since I do not understand the way that program is working yet.
You have all the steps and the files in the folder in my drive here with a doc file. Hope you will be able to open everything and I didn't miss something.
Frustrating but I won't give up.
Bruno
