Excel connector date issue with PowerBI

[robertsym]

I'm struggling to find a solution to my issue. I am using the Excel Business connector to feed a MS form date entry into a Excel spreadsheet so i can then use the date as a slicer on a PowerBI report. 

The dates do not appear to be pulling through to the spreadsheet in true date format, so the report isn't able to use a "between" slicer to filter through the dates.

Is there a way around this, perhaps using another formula on the spreadsheet?

[smorph]

Hey @robertsym ,

just for understanding:
why are you using an extra spreadsheet to get the Forms input into that?
Every MS Forms has an excel spreadsheet delivered directly.

Which as well is accessable via Power Automate and Power BI.

[robertsym]

Because our team want to be able to access certain parts of the data in their SharePoint directory, and we are using PowerAutomate to create branded PDF documents etc so it's all part of a larger workflow. 

[smorph]

Ok, got it.

As far as i understood your date is geeting to that specific spreadsheet but not in the correct format, which then gives you issues in Power BI.

Have you checked if that column in your spreadsheet has the correct format?

[robertsym]

Correct, the format of the column is correct however the data pulled across is in text format. The moment I open the spreadsheet, select the date cell and press enter it formats to the correct type and then the powerbi report lets me select the between slicer again. 

[smorph]

I see, would you please share the specific flow part including the seetings?

[robertsym]

Flow parts below, does this have soething to do with PowerAutomate not being able to pull across date formatted data but instead sends it across as text?

[smorph]

Thanks.

What does teh action "Add a row into a table" put out after you run it?
If you just need to convert time formatting without changing timezone, pleae use expression "formatdatetime()" you can use it directly in the field: Form Completion date"

Have you treid what happens when you directly write the completation date of your form into the file?

[robertsym]

The date does come across in the right format, it's just excel doesn't seem to want to accept it as a true date field until I click on the cell. 

[smorph]

After checking the action "Add a row into table" i found the option to set the dateformat.
You find it under "advanced parameters" what have you set there?

What format does the "Add a row" action output for your date?

As well after checking your screen again I see two date fields. Have you used the other one checked what happend?

[robertsym · Accepted solution]

Managed to figure it out. 

I created a new formula in the second column B1 (Date) using the below: 

=DATE(TEXT(MID(A1,7,4),"0"), TEXT(MID(A1,4,2),"0"), TEXT(MID(A1,1,2),"0"))

Column A (completed form date) held the text-based date value sent from PowerAutomate

PowerBi report is now picking up the correct format when referencing the "Date" column and the slicer is still working after testing.