Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tiolan
Frequent Visitor

Big file needs no update

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"
1 ACCEPTED SOLUTION
Tiolan
Frequent Visitor

I decide not to use Append, but having two divided tables instead.

And using measures for suming the values 🙂

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

Unfortunately there is a bug in the functionality that would do this job. Pls upvote for bugfix here:

http://community.powerbi.com/t5/Issues/Bug-in-quot-Don-t-include-in-Report-Refresh-quot/idi-p/111353...

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

Tiolan
Frequent Visitor

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

Tiolan
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.