New Table - How to Insert a Blank or Null Row for each Date in Column

[Anonymous]

Hi there!

I am trying to create a "Mapping Table" for my data model by pulling all the dates from one table along with all unique bill numbers from the same table. I used this formula to create the new table:

Mapping Table = SUMMARIZE(Order_Table,Order_Table[PICK_UP__BY_DATE],Order_Table[BILL_NUMBER])

Part of the table currently looks like this:

Is there any way that I can add a "blank" or "null" value after each date is repeated for each unique bill number?

For example, I would want a row to look like this (please see the blank "BILL_NUMBER" value below), and I would want that one blank row to exist for every date in the table:

PICK_UP_BY_DATE	BILL_NUMBER
9/13/2019	XXXXXXX
9/13/2019	XXXXXXX
9/13/2019

Any insights would be super helpful!

[mahoneypat]

FYI that Power BI automatically adds a blank row when you have rows in your fact table that don't have matching values in your dimension table(s).  Try making your relationship and then make a table visual with your bill numbers and some aggregate of the fact table (sum, count, etc.).  You should see a blank value for those rows w/o bill numbers.

Blank row in DAX - SQLBI

Regards,

Pat

[Anonymous]

Hi @mahoneypat !

Thanks for your reply. I think my issue is that I have 2 fact tables - one for "orders" and another for "quotes." Using the "quotes" table, I can determine the quote conversion rate (as a measure) by counting the non-blank bill numbers and dividing by the total quote numbers. This means that when a "quote number" is not converted to an actual sale, my bill number will remain blank. 

I also have some categories/fields on the "orders" table that I would want to view quote conversion rates against. However, the linkage between these two fact tables and my lookup table for "Date" doesn't work perfectly. 

[selimovd]

Hey @Anonymous ,

there are ways to do that, but they can become complicated.

Be aware that the sort order of a table is never guaranteed, so after creating the table with SUMMARIZE it might look like this.

PICK_UP_BY_DATE	BILL_NUMBER
9/13/2019	XXXXXXX
9/13/2019	YYYYYYY
9/13/2019	ZZZZZZZ
9/13/2019
9/13/2019
9/13/2019	XXXXXXX
9/13/2019

I am thinking why do you want to add empty rows after a repetition? What do you want to archive with that?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hey @selimovd - what are some options for doing that? 

I am looking to create the empty rows because I want to create a relationship with another table ("Quotes") which would have many instances of empty bill numbers. 

[selimovd · Accepted solution]

Hey @Anonymous ,

in general, you don't have to add the empty lines until you are planning something specific. When there is no match the blank lines from the other table will still be shown and summarized in the final visual.

However, if you want to add empty lines per day you could do an approach like that:

Mapping Table =
UNION(
    SUMMARIZE(
        Order_Table,
        Order_Table[PICK_UP__BY_DATE],
        Order_Table[BILL_NUMBER]
    ),
    ADDCOLUMNS(
        SUMMARIZE(
            Order_Table,
            Order_Table[PICK_UP__BY_DATE]
        ),
        "BILL_NUMBER", BLANK()
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thanks a lot @selimovd - this worked like a charm! 

I see what you mean about not needing to add the blank lines. I'll definitely take a look and see how Power BI maps it!