Maybe I have a too fast computer and I should use a bigger dataset, but the amount of data transferred in the dialog box during the process seems the same.Īnother interesting side effect of this feature is to make sure that an empty file would not break the transformation. I tried to run the same test but I didn’t see any performance difference. What are the performance counters you used to run this test? If you try this out yourself please let me know if it makes a difference by leaving a comment. I’ve used this technique on other slow queries and it has made a massive difference to the development experience in Power BI Desktop – one query that was taking five minutes to refresh when I closed the Power Query Editor went down to two and a half minutes.
#Power bi desktop scheduled refresh update
There is a downside to this approach: you have to hard-code the table schema that you expect your query to return, and if you change your query to return different columns you’ll have to update the table type in the last step. Look at how the graph flattens at the end… but that’s something for another blog post. This version of the query now runs in 4-5 seconds, and Process Monitor now shows that the JSON file is only read once and, obviously, reading the data once is a lot faster than reading it twice: This means that when Power BI does that zero-row filter it can now happen immediately because there’s no need to go back to the data source or execute any of the transformations in the query.
#Power bi desktop scheduled refresh code
In this case the code looks at the number of rows that are being requested (specified in the count parameter) and if that’s zero, it just returns an empty table with the same columns as the #”Changed Type” step if it’s more than zero then it uses Table.FirstN to get the actual number of rows requested from #”Changed Type”. OnTake is used when a top n filter is applied to the table returned by the query.GetRows returns all the rows that the query can return, that’s to say the table returned by the #”Changed Type” step.
It’s very easy to generate the required M code when you use the custom function that I blogged about here. The six columns listed here are the six columns you can see in the screenshot of the query output above. GetType returns a table type that describes the columns and their data types present in the output of the query.There are three fields in the record in the second parameter of Table.View that you need to change: It uses Table.View to override query folding behaviour by intercepting what happens when the table returned by the query is filtered. The OverrideZeroRowFilter step is where the magic happens. #"Changed Type" = Table.TransformColumnTypes( #"Expanded Column1" = Table.ExpandRecordColumn( Source = Json.Document(File.Contents("C:\generated.json")), You’ll end up with a query that looks something like this: let Say you have a large JSON file – for this test I generated one that is 67MB containing random data using this handy online tool – and you want to load it into Power BI. Now, thanks to a lot of help from Curt Hagenlocher of the Power Query development team I have all the details I need to blog about it. In fact, what I’m going to describe is more or less what I showed towards the end of my appearance on Guy In A Cube last year and in a few other posts, but at that time I didn’t understand properly why it worked or what the performance implications actually were. It involves some fairly complex M code but I promise you, the effort is worth it! In this post I’m going to show you a technique that can cut this wait by up to 50%. It can sometimes be frustrating to work with slow data sources or complex Power Query queries in Power BI Desktop: you open the Power Query Editor, make some changes, click Close & Apply and then wait a loooong time for your data to refresh.
0 kommentar(er)
