Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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()
)
)
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.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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?
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.
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()
)
)
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |