The datas from query editor are different with the dashboard. Bug or feature?

[ayashchuk]

Hi!

I have a script which loads the data's from API's and make some transformation.

After loading these data's to Dashboard the data's look different with the data's in Power Query.

What I've tried.

Creating the same query in Excel Power Editor and loads data to Excel sheet - this work just fine!

Recreated the same query in Power BI in new file - the problem still persist.

Cleaning all cache types and changing the colums types nothing helps.

How the data looks loke it Power Editor.

this is Dashboard version. Though the DateTime in different order the data's looks like frozen in the first row. But why the DateTime column changes well?

What is the cause of such differents and why this is not the case in the Excel version?

Please any suggestions.

🙏

[ayashchuk]

Hello everyone,

I found a solution to load unaltered data from the transform table to the dashboard by adding two steps to the transformation process.

The first step involved grouping by the date column and applying the summary operation to select all rows.

Following that, the second step was to expand the grouped column.

Surprisingly, this process kept the data structure intact, and the data was then loaded to the dashboard without any changes.

combinedTable = Table.Combine(veryBigTables),
//Loading data to the Power BI Dashboard from THIS point Lead to detailed info meshed up (the first row with detailed information of item duplicated along all other rows with only dates column was different)

// Grouped Rows
#"Grouped Rows" = Table.Group(combinedTable, {"DateUntil"}, {{"Days", each _, type table [Name=text, "Column1, hour"=text, Column2=text, Column3=text, Column4=text, Column5=text,  DateUntil=datetime]}}),

//Expanded Days
#"Expanded Days" = Table.ExpandTableColumn(#"Grouped Rows", "Days", {"Name", "Column1, hour"=text, Column2=text, Column3=text, Column4=text, Column5=text, DateUntil}, {"Name", "Column1", Column2,Column3, Column4, Column5, "DateUntil.1"})
//Loading data to the Power BI Dashboard from THIS point Lead to Success!!
in
#"Expanded Days"

I'm curious about the purpose of these steps. Could someone please assist me in either refining or removing this decision?

Thank you in advance for your help!

[ayashchuk]

Hello,

I apologize to the community. The issue was addressed by adding a random number to the Web.Contents request.

url = url & "&cache=" & randomNumber

Further data manipulations did not change the situation.

It appears to be a caching issue.

I still don't understand why the data displayed differently in the transformation compared to the dashboard.

The  two steps mentioned in the previous post was unnecessary.

Regards

[v-tianyich-msft]

Hi @ayashchuk ,

In Power BI, Time or DateTime type data can be presented as original dates or as hierarchical data.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[ayashchuk]

Hi! 

Sorry, but this did not help.

Though the date column changed it representation from Year, Month, Day to just DateTime column, the details are duplicates from first row to others row of the same item. 

The last step of transformation. I've added the datetime column and now the i+1 column with no luck.

In the transformation editor - before data loads to Power BI dashboard, these data looks like :

[ayashchuk]

Wow!

After trying to re-order the columns by ItemName desc so the all items details shows near one by one. The details start to duplicate the first row of the details in the transformation editor too.

So this behavior occurred as a result of ordering.

Why is so happened?

[amitchandak]

@ayashchuk , Date has time and has been split in Table visual. If they are not from the same table then I doubt the join is missing.

If the columns are from the same table. I doubt aggregation of measure causing it

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[ayashchuk]

Thank you @amitchandak for your reply!

Tried to find the joins in my data. But table have joins before adding the DateTime Row and No Row.

Also, by adding the ordering the details corrupted and PBI shows only first detaied row at all columns.

I am novice to PBI and did not know why is happened.