Historical data

[AGo]

Hello,

is it possible in PowerBI (using Power Query) to append historical data not refreshing to a refreshing same-structured table?

After that I wouldn't be able to connect to the DB anymore, then I'd need to operate to the append table (maybe creating index) without PowerBI asking me to connect the historical data another time.

I know I could do this with DAX UNION function, but I'm asking specifically about Power Query.

It's 1 billion rows and it's very unpractical to refresh everytime I operate and so would be incremental refresh.

Thanks

[v-lionel-msft]

Hi @AGo ,

"is it possible in PowerBI (using Power Query) to append historical data not refreshing to a refreshing same-structured table?"

According to your description, you seem to want to append historical data in a non-refresh way in "Edit Queries". 
You can select "Home" -> "Append Queries" options to achieve it, but if you use "Direct Query" or "Live connection" mode, you can't use the feature.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@AGo You should be looking into incremental refresh. There are many posts on it and here is link to microsoft doc. And luckily recently incremental refresh is released in Power BI pro so you are covered to use it without premium.

Would appreciate Kudos 🙂 if my solution helped.

[AGo]

I'm sorry but as I said I can't use incremental refresh because it has two big limits, it needs an active connection and it refreshes data everytime you operate. For example if I have a value column and I create a new column that returns [value]+1 it doesn't use stored data but pretends to refresh all the data from the start.

[parry2k]

@AGo well you have to make sure you know what columns you need. It is like traditionally building a warehouse and after 10 years of data, if you need to add new column, you have to backfill your warehouse and similarily, if you are adding new columns etc, ofcourse you have to update your dataset (and again full refresh will be one time) and then it will be inremental refresh. Not sure what  you expect the solution suppose to be if incremental refresh is not the right approach here.

Also I'm not sure what you mean by active connection?? Data refresh required connection.

[AGo]

yes, the dataset will never change and I need to add new columns but not from the DB but calculated from the dataset itself. So there's no reason for refresh the dataset. It seems that PowerBI needs to refresh data for every change in derived data also if the fundamental data are the same. There is no sense in pretending to refresh all the historical data if I'd like to add a column like valueA*valueB, PowerBI has all the information to operate this multiplication. Most of the time the limit of 2 hours for the first refresh in incremental refresh is too low, using PowerBI desktop to query the large amount of data is the most efficient way too freeze it.
Maybe someone can answer with a solution to keep historical data freezed and allowing to operate like we do in dax but directly in power query.

[parry2k]

@AGo if you add new columns as DAX expression you don't need to refresh the dataset, it is always added on existing dataset. You need to know basic fundamentals , difference betwee adding column using power query or getting new columns from datasource vs calculating column using DAX. Wiht DAX, it works on existing loaded data.

[AGo]

Yes I know, but unfortunately some functions for derived data are not available in dax (like intelligent unpivoting and row number indexing without ranking). That's why I need to literally freeze historical data besides that it's impossible for me to query historical data in 2 hours limit or mandatory refreshing datasource everytime I apply changes on derived data functions. I hope there's a solution.

[parry2k]

@AGo you can have historical data in dataflow and then combine it with new data. With dataflow you can freeze historical data and only run dataflow when you need to refresh historical data.

[PBILover]

@AGo : How did you achieved this?