power bi scripting (data load)

[ddumas]

I am new to Power BI.  Is there a scripting component that allows me to write custom code to load all my data into a data model?

One example is that I may have 1 flat table with dimensions embedded in.  I wish to create a star schema "on-the-fly", by selecting distinct values from the flat table for each of the dimensions, and during this process, create integer (surrogate) keys to the dimensions.  Then I would load in the facts, with just keys and measures.  At the end, I would have:

On large but narrow fact table

Serveral dimensions

Thus creating a star schema.  I would then drop / not use the large wide original fact table .

I do not have the luxury of creating database tables in this scenario.  I can only read from the database or read fro a flat file.

Thanks,

Dave

[MarcelBeug · Accepted solution]

Your "scripting component" is Power Query, accessible via the query editor.

Typically each query will result in 1 table, so you need (at least) as many queries as the number of resulting tables in your datamodel. You can also have so called "staging queries" with intermediate results that can be referenced by other queries for further transformations resulting in the actaul table in your datamodel. 

Notice that Power Query is more than just a "scripting component": it has a complete underlying programming language ("M").

[erik_tarnvik · Accepted solution]

This is fairly easy to do in Power Query as suggested by Marcel, let me offer a minimal example and you should be able to go from there. Assume we have a table called Products:

Product	Color
A	Blue
B	Red
C	Green
D	Blue

First, bring this table into Power Query. Remove column Product (and all other columns except the one you want to index in your real world table), select the remaining Color column and click Remove Rows - Remove Duplicates. You should now have a table with the unique colors. Create an index column for this table with Add Column - Index Column. Rename the index column to something appropriate such as ColorIndex. Rename the query you just created to Colors.

Now, bring in the Products table again using Recent Sources. Use the Merge Queries function and select the Color column from Products. Then select the Colors table and select the Color column from that table. Click OK. The result is a new column Colors with a table on each row. Expand the tables by clicking the icon in the upper right corner of the column header. Click OK in the dialog box. Remove the two remaining color text columns and leave only the numeric index column. You may want to rename that column to something sensible.

You are now left with a table Products with an index to the table Colors. Now rinse and repeat for all other dimensions in your fact table.

[MarcelBeug · Accepted solution]

Your "scripting component" is Power Query, accessible via the query editor.

Typically each query will result in 1 table, so you need (at least) as many queries as the number of resulting tables in your datamodel. You can also have so called "staging queries" with intermediate results that can be referenced by other queries for further transformations resulting in the actaul table in your datamodel. 

Notice that Power Query is more than just a "scripting component": it has a complete underlying programming language ("M").