Big file needs no update

[Tiolan]

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"

[ImkeF]

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...

[Tiolan]

Yes 🙂

But what can we do?

What is the best practice to get big files updated?

What is the ways ro make updating faster?

[ImkeF]

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

[Tiolan · Accepted solution]

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

And using measures for suming the values 🙂

[ImkeF]

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)