Forum Discussion
power bi scripting (data load)
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").
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.
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.