Managing "dummies" in a data model

[PBI_hlpr1]

Hi there,

I have a typical sales star data model with two fact tables (one for Actual sales coming from the ERP and one for budget sales coming from a spreadsheet. I then have dimension tables (clients, products, etc.) also coming from the ERP.

I am trying to get rid of blanks originated by use of dummies in the budget sales fact tables. This happens as some lines do not contain a standard client or product code but just a generic "dummy" or similar text. Of course my dimension trables do not contain those dummy codes and are returning "blank" in the report slicers. Of course I cannot add the dummies to the dimension table as is resident in the ERP. Is there a way eventually to append the dummies descriptions to the related dimension table queries?

Hope to have been clear enough...

Thanks,

C.

[Ahmedx]

look hir
https://www.spguides.com/remove-blank-from-power-bi-slicer/
https://www.sqlbi.com/articles/blank-row-in-dax/
https://www.youtube.com/watch?v=23qemkDqMVY&ab_channel=GuyinaCube

[PBI_hlpr1]

Thanks Ahmedx, the third link below is actually reflecting the situation I have. Unfortunately I cannot "sanitize" my data as the Dimension tables are coming from the ERP. Is there a way to add the missing product codes in the query for that dimension table?

[v-jingzhang · Accepted solution]

Hi @PBI_hlpr1 

You can use Power Query to filter those "dummy" rows, then append that query to the original Dim table. Apply the change to Power BI Desktop. 

Filter by values in a column - Power Query | Microsoft Learn

Append queries - Power Query | Microsoft Learn

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[PBI_hlpr1]

Thanks Jing,, this sounds good and is very similar with what I have done in the meantime. I have created an excel file with all the dummy lines, create a related query in powerBI and them appended that quesry to the existing one. Just one question. The worksheet containing the dummy lines does necessarily need to have the exact data layout (field names) of the dim table to be appended to? I am asking as I am getting error of not allowed "nulls"

[v-jingzhang]

Hi @PBI_hlpr1 

With Append queries feature, Power Query performs the append operation based on the names of the column headers found on both tables, and not based on their relative position in the headers sections of their respective tables. The final table will have all columns from all tables appended. In the event that one table doesn't have columns found in another table, null values will appear in the corresponding column. This method accepts null values. 

How did you append the queries currently? 

Best Regards,
Jing

[PBI_hlpr1]

Hi Jing,

I am actually using append queries but as the receiving table is one the one-side of a 1-to-many relation does not accept blanks... I had therefore to fill in the appended table all blanks with ' or 0.

But it's ok - now it works.

Thanks!!

C.

[amitchandak]

@PBI_hlpr1 , Create a measure removing those account 

calculate([budget Measure], filter(budget,not budget[Client] in {"ANC", "DEF"}) )

or

calculate([budget Measure], not(isblank(budget[Client] ))  )

[PBI_hlpr1]

Thanks Amitchandak,

the below unfortunately is not helping as I do need that data in my model. I need instead a way to virtually "ADD" the missing codes to the dimension tables.