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

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