Forum Discussion
Power BI Query Editor Creating Dimensions and Adding the -1 No Data Row
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?
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:
powerbi - Power BI Query Editor - Inserting Rows to the Bottom of a Table
Best Regards
2 Replies
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:
powerbi - Power BI Query Editor - Inserting Rows to the Bottom of a Table
Best Regards
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
