Forum Discussion

Helper II

4 years ago

Solved

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/0...

Pragati11
4 years ago
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

Super User

4 years ago

I am assuming your new Date column with merged dates is of TEXT datatype.

In that case just create a slicer, move this new Date column and select dropdown type on the slicer.

Share some screenshot here, as it can help us what you are seeing at your end? 🙂

Thanks,

Pragati

Malsk1_1
Helper II
4 years ago
Pragati11 Both are Date columns but in the date picker i am assuming they would need to be in text due to the text at the end of the date from table 2.
- Pragati11
  Super User
  4 years ago
  HI Malsk1_1 ,
  
  Yes your columns need to be text type now as one of the values have a text with date value.
  
  Thanks,
  
  Pragati
  - Malsk1_1
    Helper II
    4 years ago
    Pragati11 @I am assuming that I need to create a calculated field to say something like this.
    
    if max date from table 2 does not exist in table 1, then show the max date in table 2 & Flash else show the dates from table 1.
    
    how can I write this?
- Malsk1_1
  Helper II
  4 years ago
  Pragati11 the date filter should display like this :
  - Pragati11
    Super User
    4 years ago
    Hi Malsk1_1 ,
    
    Sorry I didn't understand.
    
    Is the slicer not displaying values as in the screenshot? You already have column with all the values right?
    
    Are you having issues with displaying them in an order? If yes, then do one thing.
    
    Go to Power Query Editor and add an Index column to your table. Something like below:
    
    Then apply your changes. Go to Data tab in Power BI and sort your textDate column by Index column:
    
    Once you do that, you will get following:
    
    Now create the slicer and you will get the way you want it:
    
    Let me know if this works.
    
    Thanks,
    
    Pragati