Data Model scenario in Power Query

Power Query
1

Hi everyone,

I was hoping to get some guidance on a data model scenario that I need to solve.

A data dump from a legacy system is in an Excel file and needs to be kept inside a Power BI file for historical reference purposes.

The ask is that this excel data dump can’t be stored inside a SharePoint or any other location other than the Pbix file.

I have tried to create a data table in Power query so that I can dump the data records here and it will have no linkage to SharePoint site or any storage option, however, this dataset has nearly 60,000 records and so there is no direct copy and paste option. I have to make data samples and input set by set.

Is there any better or easier way to do this rather than copy and paste a set 3000 records each.

I have thought to import the model and disable the load option however it is not working for the ask of not to store in any other location other than Pbix file.

Appreciate any better suggestions.

Attachiung the sample data and screen shots of the options I tried for for quick reference

Power BI Option 1
Power BI Option 11160×369 18.8 KB

Test WOrkbook.xlsx (141.8 KB)

2

Hi @nadeena_mini ,

I’m not quite sure what you try to achieve.

Exploring different options :
Could you perhaps get data directly from legacy system (and have snapshots data there or you need just for this one report history purpose import in PBI (currently export/import via Excel )?

If needed you can Get data initially via Excel in PowerQuery you have
options Enable Load and Include in refresh report

image

More details about Data Transformation and Modelling you can find at Sam’s course:

1 Like
3

Hi @mspanic ,

Thanks for getting back to me.

Yes it is a-bit tricky to explain.

It is like I am unable to use export file option and need to load a data having 60000 rows.

The requirement is that we can’t save a file in Sharepoint or One drive that holds this data and load to Power BI for various security reasons.

The ask was to check for option to load the data using any other way.

There is an option to create a table in Power query and dump the data however It is not allowing to paste the entire data at once.

That is where I am struggling at the moment and was looking for any better suggestions.

4

Hi @nadeena_mini ,

Could you import / get data once (for historical purposes) directly from legacy system?

In PowerQuery you can import data from various data sources - some of them you can find on the following list:

5

Hi ,

Let me try if that is possible. Thanks for sharing the possibilities.

1 Like