Power Apps Dataflow - Data Transform is not working when pulling table in Power Bi

[lardo5150]

Here is what we are doing...

I have two SPO lists.  One is static, never changes.

The other is where we assign cases.

At the end of each day, the goal is to pull this data into the dataverse, then remove all entries, so the next day is a clean slate.

I have a dataflow setup.  It creates two tables, called Assignments and Engineers (Assignments is the one that changes).  These are pulling from the corresponding SPO List.

In my dataflow queires, I am converting the assignment date from UTC, to CST.

The assignment date is in the format of date/time with a column format at default of ABC123

I create a new custom column of date/time/zone, and change assignment time to reflect the date/time/zone

DateTime.AddZone([AssignmentTime], 0)

I then add another column to change the new custom column with UTC, to CST

DateTimeZone.SwitchZone([UTCTimeZone], -6)

I then merge the two tables into a third table (as I need some information from both).

The new merge table shows the correct times in the new assignmenttime_CST column.

Now, when I load the third table (merge table) into Power Bi so I can start building a report, the columns I created show UTC still, not CST.

What is happening here?

I am trying to do all the transforming of the data within PowerApps in that table, as that is where I want the data stored.  When I load up Power Bi and connect to the dataverse, I am choosing Direct Query, as I do not want to store the data, I want to just query the merge table.

Is there something with how Power Bi views those custom columns since they are not in DAX?

Instead of changing the times in the Dataflow, can you do it in Power Bi, even though I am doing a direct query?

I am at a loss of why this is happening.

FYI, I am doing some other transforming of the data, like getting the month out of the assignmenttime and converting that number to the month name.  That is working fine.

[v-angzheng-msft · Accepted solution]

Hi, @lardo5150 

So you mean the date/time/zone are not displayed correctly in Power BI.

After loading the other two tables into Power BI as well, is the time zone of the column displayed correctly, and is the date time zone of the other columns(UTC) displayed correctly?

Try to add a time zone and then convert the time zone to UTC and then switch the time zone, that is, add a step to convert the time zone to UTC in the above steps.

A=DateTime.AddZone([AssignmentTime], X)

B=DateTimeZone.ToUtc(A)

C=DateTimeZone.SwitchZone([UTCTimeZone], Y)

Best Regards,
Community Support Team _ Zeon Zheng

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