Log In
Register
Home
December 18, 2018
Offline
Hello, in conjunction with a previous post of mine:
https://www.myonlinetraininghu.....mps#p19048
I've been able to modify my table of 30k+ records and return the information I'm looking for (I believe), however, even when wrapped in Table.Buffer, it refreshes at roughly 10 rows per second and will take quite a while to get through the 30k.
Is this likely due to the calculation involved in power query just being too large, or something I'm not doing with Table.buffer?
November 8, 2013
Offline
Hi Adam,
The FilterTable function is executed at each row, filtering the source data to get the exit time for each record is not efficient.
It can be improved, instead of filtering the entire initial table, we can filter a smaller table containing Exits only, already sorted to avoid sorting at each record in the FilterTable function. Filtering a smaller table already sorted should be faster.
Source = Excel.CurrentWorkbook(){[Name="AdamS"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Field Date Time", type datetime}}),
FilteredRows = Table.SelectRows(ChangedType, each ([Direction] = "Entry")),
Exits=Table.SelectRows(ChangedType, each ([Direction] = "Exit")),
ExitsSorted = Table.Buffer(Table.Sort(Exits,{{"Field Date Time", Order.Ascending}})),
FilterTable = (tbl as table, Person as text, Company as text, EntryDate as datetime)=>
let
Filtered = Table.SelectRows(tbl, each ([Name] = Person and [Company] = Company and [Field Date Time] > EntryDate))
in Table.FirstN(Filtered,1),
AddedCustom = Table.AddColumn(FilteredRows, "Exit", each FilterTable(ExitsSorted,_[Name],_[Company],_[Field Date Time])),
#"Expanded Exit" = Table.ExpandTableColumn(AddedCustom, "Exit", {"Field Date Time", "Direction"}, {"Exit.Field Date Time", "Exit.Direction"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Exit",{{"Exit.Field Date Time", type datetime}})
in
#"Changed Type"
If you can prepare sample data for 1-5 thousand records or more, I can test it on my side to see what else can be improved
November 8, 2013
Offline
Because you mentioned power BI, you could write a measure to calculate the Exit dates...
Split the initial data into 2 tables (one for Entry, one for Exits)
In Entry table, add a measure to calculate the Exits from Exits table.
Should be much faster than PQ, which is mostly for data transformations, not complex calculations.
December 18, 2018
Offline
Thank you for your responses, so very much.
I have been struggling mightily with this and I worry a great deal my current solution won't be sustainable. With regards to Power BI, where our company's solution is currently implemented, a fantastic user in the official PowerBI forum provided this calculated column for me which returns the total number of minutes on site, which I then convert to hh.mm:
November 8, 2013
Offline
Hi Adam,
The last query provided took 2 min and 20 seconds on my PC to process those 20k records, generated a little over 10k rows.
If we extrapolate, 100k records will take around 12 min. Will that work for you?
December 18, 2018
Offline
Hi Catalin,
I suppose it would get the job done. Though for our purposes, where ~35k = 1 month ~110k = 3 months and and ~450k = 1yr, I'm not sure anyone has the appetite for 3 months. Maybe it's better than 1.
I guess my hope in all this was a stronger solution that wouldn't burden the system in the same way the calculated DAX column does, yet also performs faster and gives us a whole year view.
My suspicion is that using your query for a rolling 30 days is the most viable, stable and efficient method of accomplishing the goal.
If you wouldn't mind answering a couple questions. 1) Can you think of a better way of utilizing the DAX solution that accomplishes the same goal with less strain 2) Assuming I attempt to implement your PQ code with our SharePoint folder source, I was getting errors. It only worked when I ran the query against a flat table. Would you have any thoughts on that?
November 8, 2013
Offline
PP is much faster, that's why I mentioned it.
See attached PP version, takes a few seconds for 20 k test data.
I split the data into Entry and Exits, the calculated column formula is very simple, no relationship needed.
November 8, 2013
Offline
Getting data from sharepoint folder is not the same thing as getting data from local file or folder, you have to adjust the query.
Microsoft 365 has the option to get data from SP folder: Get Data> From File> From Sharepoint Folder
December 18, 2018
Offline
Thanks for sending along the sample file, though unfortunately it's confused me a little. I see Query1 is the AdamS table, Entry2 has the corresponding Exit timestamps, Entry fails to load - it provides "Expression.Error: 5 arguments were passed to a function which expects between 2 and 4." and Exits is loaded to the data model, which I'm assuming populates the pivot table.
When I refresh Query1 it took about 2:15, as well as Entry2. For only ~10k records this seems like a high amount of time, especially if we hope to go over 1yr of data.
Also, just to be clear, the DAX measure I'm using in Power BI is from a SharePoint folder, (which I've also tried using in Excel Power Query). The SharePoint Folder model appears to not work with this Excel query you're providing.
November 8, 2013
Offline
Query1 and Entry2 are 2 different PQ solutions, the processing time is almost the same, but I doubt that it can be made faster than it is.
Entry and Exits are loaded to data model. If you get an error in Entry, you might have an older version of PQ.
Try = Table.AddIndexColumn(FilteredRows, "Index", 0, 1) instead of = Table.AddIndexColumn(FilteredRows, "Index", 0, 1, Int64.Type)
Again, just to be clear:
Microsoft 365 has the option to get data from SP folder: Get Data> From File> From Sharepoint Folder
If the PP version is acceptable, you can replicate the structure in your PBI model, you have everything you need in the file provided.
The SharePoint Folder model appears to not work with this Excel query you're providing.
there can be many reasons for this, my guess is that you did not updated properly the query to the PBI version.
Of course, without seeing what you did and without proper error description, I have to take your word for it 😉
December 18, 2018
Offline
I really appreciate you taking the time to support this. I am building two reference tables in PBI from the Gate Scans source, (Entry and Exit). I will attempt to replicate what you've done in PP. I see you created a key for Name and Company, would the Unique SBIID1 column not be sufficient?
November 8, 2013
Offline
I used the fields you have indicated originally:
Is there any easy way to subtract one line from the previous line given the name and company match[..]
Use as key whatever you know it can be used as an identifier. I have no idea what that SBIID represents, you know best.
December 18, 2018
Offline
I was optimistic about the PP solution initially. I had no idea how to make it work in PBI so I went back to your Excel file and removed the two tables I didn't need, and then swapped the AdamS dummy table with the correct information (100k rows). Once I did that, it removed the two columns for Entry and Exit time stamps from the pivot table. I've attempted to re-add the Entry column first, and it's been taking at least 20 minutes to put back on there (still seems to be working away as I type this). Something tells me the PP solution may not be viable after all.
GateScans query is now 114k, Entry 57,834 and exit 56,332.
I feel like I've reached the end of the road though.
November 8, 2013
Offline
Columns are removed from a pivot table ONLY if you overwrite the headers as well when you paste the real data into source table..
Why are you replacing headers? Paste data.
December 18, 2018
Offline
Please forgive the delay in responding. I had to change the data source to connect with our actual source data, at which point it lost those two columns, which I was unable to re-add.
I ran the report last night using the original filtering solution (in the link at the very top), and it worked, though it took about 7 hours to get through the 59k rows for the three months. I meant to only have one month selected. That may be a solution, though, to the point of this topic, I've got the code in Table.Buffer and it didn't seem to increase the speed at all which tells me I may not quite understand what it does.
Finally, in the file you sent me above, where you had a query for Entry scans and another for Exit scans, can you explain how you intended to rejoin the data? I saw only one index column and I couldn't figure it out (which is why I re-tried the original solution).
November 8, 2013
Offline
can you explain how you intended to rejoin the data? I saw only one index column and I couldn't figure it out (which is why I re-tried the original solution).
I thought it's pretty clear it's a power pivot solution. Therefore, you have to open power pivot window to see the Exit Time column added to the Entry table.
That Exit Time formula simply reads the exit time from the Exits table.
1 Guest(s)
