Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hoyty
Regular Visitor

SharePoint list - using created date / time in date filter?

The created column in SharePoint is a Date / Time field and when brought into Power BI is seen as a text field rather than a date field. How can you filter on date such as After 2022-09-01 since it isn't seen as a date? Do I need to to a measure or transform to create a calculated column?

1 ACCEPTED SOLUTION
hoyty
Regular Visitor

Replying to myself in case it helps someone else later. Select the column on the far right list. Then at the top change the data type from text to date. Once this is done you can now filter it like a standard date field. Once I figured it out super easy.

hoyty_0-1678813685636.png

 

View solution in original post

3 REPLIES 3
ichavarria
Solution Specialist
Solution Specialist

Hi @hoyty,

 

To filter on date you can create a calculated column to convert the text field to a date field. Here's how you can do it:

  1. Open the Power Query Editor in Power BI by clicking on "Transform Data" in the Home tab of the ribbon.

  2. Select the table that contains the "Created" column.

  3. Right-click on the "Created" column and select "Change Type" > "Using Locale" > "Date/Time".

  4. If the column contains any errors, Power Query will prompt you to correct them.

  5. Once the "Created" column is converted to a date/time data type, click on "Close & Apply" to save the changes.

  6. Now you can create a calculated column by clicking on "New Column" in the Modeling tab of the ribbon.

  7. Use the following formula to create a calculated column that extracts the date from the "Created" column:

    = DATE(YEAR([Created]),MONTH([Created]),DAY([Created]))

  8. Name the calculated column "Created Date" or something similar.

  9. Now you can use the "Created Date" column to filter your data by date, such as filtering for dates after 2022-09-01.

By creating a calculated column that extracts the date from the "Created" column, you can now filter your data based on date values.

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

Thanks, the method I found was way easier. Yours might work with dirty data.

hoyty
Regular Visitor

Replying to myself in case it helps someone else later. Select the column on the far right list. Then at the top change the data type from text to date. Once this is done you can now filter it like a standard date field. Once I figured it out super easy.

hoyty_0-1678813685636.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors