Reply
Highlighted
Frequent Visitor
Posts: 12
Registered: ‎11-18-2016
Accepted Solution

Big file needs no update

[ Edited ]

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"

Accepted Solutions
Frequent Visitor
Posts: 12
Registered: ‎11-18-2016

Re: Big file needs no update

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

And using measures for suming the values Smiley Happy

View solution in original post


All Replies
Super Contributor
Posts: 924
Registered: ‎09-06-2015

Re: Big file needs no update

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

Frequent Visitor
Posts: 12
Registered: ‎11-18-2016

Re: Big file needs no update

[ Edited ]

Yes Smiley Happy

But what can we do?

 

What is the best practice to get big files updated?

What is the ways ro make updating faster?

 

Super Contributor
Posts: 924
Registered: ‎09-06-2015

Re: Big file needs no update

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

Frequent Visitor
Posts: 12
Registered: ‎11-18-2016

Re: Big file needs no update

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

And using measures for suming the values Smiley Happy

Super Contributor
Posts: 924
Registered: ‎09-06-2015

Re: Big file needs no update

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)