How to create a large static table in a pbix file?

[DeonDP]

Hey Guys

I need to create a large static table in my Power BI file - approx 46,000 rows, but it seems that a static table in Power BI desktop is limited to 3,000 rows only. 

I would like to keep the static file within the pbix file, and not linked to an outside source, and I certainly don't want multiple static tables in my model if I can avoid it.

Any tips or recommendations on how to get this done will be much appreciated.

Thanks 

Deon

[artemus]

Encode a csv version of your file in Base64 (there are various online tools that do this). Copy and paste this as a text query (just paste the text into the formual bar as is, or using the advanced editor enclosed in "").

Create a new Query using Binary.FromText(encoded data). When you do this, the UX should automaticfally detect it is csv and load it in.

[DeonDP · Accepted solution]

Hi Everyone

Artemus set me on the correct path, but it took a while for me to get my head around it.

I initially went the Binary code route as suggested, but going the Csv route in actually easier in my opinion. Csv to Power Query – not via first Binary coding)

So just for the benefit of anyone who needs to create a large static table (in my case approx 46,000 rows) with headers in Power Query, this is what you need to do:

This is a sample of my Csv Dataset:

CompanyID CostCentre New Cost Centre Cost CentreDescription Cost Centre2
1 10 100 PRELIMINARIES & FEES 1-100
1 101 PRELIMINARIES & FEES 1-101
1 102 PRELIMINARIES & FEES 1-102
1 103 PRELIMINARIES & FEES 1-103
1 104 PRELIMINARIES & FEES 1-104
1 105 PRELIMINARIES & FEES 1-105
1 106 PRELIMINARIES & FEES 1-106
1 107 PRELIMINARIES & FEES 1-107
1 108 PRELIMINARIES & FEES 1-108

This is the Mcode to get the data loaded:

(I would normally just open the Advanced editor and paste it in)

let
Source = "CompanyID CostCentre New Cost Centre Cost CentreDescription Cost Centre2
1 10 100 PRELIMINARIES & FEES 1-100
1 101 PRELIMINARIES & FEES 1-101
1 102 PRELIMINARIES & FEES 1-102
1 103 PRELIMINARIES & FEES 1-103
1 104 PRELIMINARIES & FEES 1-104
1 105 PRELIMINARIES & FEES 1-105
1 106 PRELIMINARIES & FEES 1-106
1 107 PRELIMINARIES & FEES 1-107
1 108 PRELIMINARIES & FEES 1-108
",
ToCSV = Csv.Document(Source)
in
ToCSV

Once it has been loaded into Power Query you will see the data but it looks a little weird.

Next step is to right click on the Column Header -> select Split Column -> By Delimiter (or whatever you need to split the data by)

That will give you a table where the headers are on the second row.

Next step is to select Transform on the top ribbon and select Use First row as headers.

Now you can obviously continue and make changes as required.

Hope this post will eliminate the struggle I had for anyone else wanting to do the same.

Cheers

D