Forum Discussion
Adding a Text value in a date filter
Hi,
I have 2 tables
| Table 1 | Table 2 | |
| Baseline Date | Baseline Date | |
| 01/01/2021 | 13/10/2021 | |
| 01/02/2021 | ||
| 01/03/2021 | ||
| 01/04/2021 | ||
| 01/05/2021 | ||
| 01/06/2021 | ||
| 01/07/2021 | ||
| 01/08/2021 |
I have merged the tables and therefore have a complete list of dates covering them both, however when i create a date picker filter for the merged dataset, i would like to show all dates from the combined list, however for the date that has come from Table 2 I would like to show it with 13/10/2021 Flash - as below:
| Date Picker |
| 01/01/2021 |
| 01/02/2021 |
| 01/03/2021 |
| 01/04/2021 |
| 01/05/2021 |
| 01/06/2021 |
| 01/07/2021 |
| 01/08/2021 |
| 13/10/2021 (Flash) |
How can i do this?
Hi Malsk1_1 ,
So you don't have a date column with all those values.
Your query says, you have merged the tables and got the dates in place, so I got confused.
Suppose you have following 2 tables:
Table1
Table2
Make sure datatype of both Date columns in both tables is set to TEXT.
In Power Query editor, click on Table2 and add a custom column:
Then add following formula:
You will get the following:
Now I will remove previous column from Table2 and just keep the new caluclated column as follows:
Now in both Table1 and Table2, I am giving name to the columns as Date as this will help me in next steps and their datatype as Text.
In Power Query editor, you will have to append them together. Make sure the column name is same in both the tables.
Click on Table1 on the left and on the top ribbon select Append Queries as shown:
Then the following window:
You end up the required output:
Now add an index column to this table, to make sure your slicer displays data in right order:
Then apply your changes. Go to Data tab in Power BI and sort your Date column by Index column:
You will end up with slicer in the required format:
Thanks,
Pragati
