Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
There's 2Gb file "Sales2011-2016", containing data from last 5 years. It doesn't need to be updated.
And the second one "Sales2017". It must be updated every day.
If i append these two tabled in Query Editor, it starts to update both files, and it is VERY LONG PROCESS...
Is it possible to make it faster?
Big one:
let Source = Json.Document(File.Contents("\\nas-01.ktc.local\Shara\PowerBi\mosntro\bi_sales_2011-2016.json")), #"Развернутый элемент Column1" = Table.ExpandRecordColumn(Table.FromList(Source[Data], Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"ПериодСекунда", "Registrator", "Номенклатура", "Склад", "Manager", "Подразделение", "Партнер", "Выручка", "Количество", "Себестоимость"}, {"Момент часу", "Документ", "НоменклатураUUID", "СкладUUID", "Менеджер", "ПідрозділUUID", "ПартнерUUID", "Виручка", "Кількість", "Собівартість"}), #"Добавленный запрос" = Table.Combine({#"Развернутый элемент Column1", #"Продажі 2017"}) in #"Добавленный запрос"
Small one:
let Source = Json.Document(Web.Contents("http://ws-01.ktc.local:5000/ue/ver1/bi_sales/transfer", [Timeout=#duration(0, 0, 10, 0)])), #"Развернутый элемент Column1" = Table.ExpandRecordColumn(Table.FromList(Source[Data], Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"ПериодСекунда", "Registrator", "Номенклатура", "Склад", "Manager", "Подразделение", "Партнер", "Выручка", "Количество", "Себестоимость"}, {"Момент часу", "Документ", "НоменклатураUUID", "СкладUUID", "Менеджер", "ПідрозділUUID", "ПартнерUUID", "Виручка", "Кількість", "Собівартість"}) in #"Развернутый элемент Column1"
Solved! Go to Solution.
I decide not to use Append, but having two divided tables instead.
And using measures for suming the values 🙂
Unfortunately there is a bug in the functionality that would do this job. Pls upvote for bugfix here:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Yes 🙂
But what can we do?
What is the best practice to get big files updated?
What is the ways ro make updating faster?
Unfortunately I see no possibility to speed this up.
You can try different file formats (instead of JSON), but this is just a vague guess out of desparation 🙂 Haven't tested these out (maybe csv or txt - don't try xls, that's much slower !!)
Help finding supporters for bugfix. I tried to do my best on Twitter: https://twitter.com/TheBIccountant/status/818933857445617665
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I decide not to use Append, but having two divided tables instead.
And using measures for suming the values 🙂
Then you're good to go: Just deselect "Include in Report Refresh" in the query-editor and load to datamodel 🙂 This will work & will not be refreshed (at least not on my machine).
(A UNION in DAX is the current workaround for the append)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |