Power BI Query Editor Creating Dimensions and Adding the -1 No Data Row

[DebbieE]

I usually have a SQL DB as my source where I prep the dims and facts for Power BI but I have to do a project where I go from source to Power BI.

I always add a default row in every dim where there is no data, so for example

DimActivity

Key  Name         Description

-1, 'No Activity', None Set' 

And this is easy in SQL DB. But is there an easy way you can do this just in power BI Query Editor?

[Anonymous · Accepted solution]

Hi @DebbieE ,

You can copy and paste the following applied codes in  your advanced editor to implement it:

let
    Source = Sql.Database("servername", "yourdbname"),
    dbo_DimActivity = Source{[Schema="dbo",Item="DimActivity"]}[Data],
    #"Insert default row" = if Table.RowCount(dbo_DimActivity)=0 then  Table.InsertRows(dbo_DimActivity,0, { [ Keys = 1, Name = "No Activity", Description = "None Set"] }) else dbo_DimActivity 
in
    #"Insert default row"

You can get more details from the following links:

Table.RowCount

Table.InsertRows

powerbi - Power BI Query Editor - Inserting Rows to the Bottom of a Table

Best Regards

[DebbieE]

Oh fantastic. Than you so much. i went around the houses with this by replicating the table. Changing a key from 0 to -1. Removing everything not -1. removing all other columns. And then appending into the previous table. this is going to get rid of loads of steps

I need to do some more work on this because the above isnt working at the moment because im not adding into an empty table