cancel
Showing results for 
Search instead for 
Did you mean: 

Daily changing pivot table, dashboard error

I have this data: Where I have found workaround but need permanant solution.

 

Original Data - from Oracle db which pulls last 4 days of data
Column 1Count (IDs)Date   
A5622-Apr   
B78922-Apr   
C231222-Apr   
D322122-Apr   
A 23-Apr   
B345423-Apr   
C23123-Apr   
D323223-Apr   
A485624-Apr   
B23224-Apr   
C35324-Apr   
D125424-Apr   
A 25-Apr   
B213425-Apr   
C34225-Apr   
D35425-Apr   

 

 

I have used pivot table function to get below table: After pivot I have manually selected all dates as my columns.

 

This is expected output.

 

Output    
Column 122-Apr23-Apr24-Apr25-Apr
A56 4856 
B78934542322134
C2312231353342
D322132321254354

 

 

But when I refresh data next day, dashboard will be broken, since I am manually selecting days to display last 4 days of data in Power BI.

 

Can you help me achieve this without manually adding dates everyday?

 

Thank you in advance.

Status: Needs Info
Comments
Moderator

Hi @c5966

 

>> But when I refresh data next day, dashboard will be broken, since I am manually selecting days to display last 4 days of data in Power BI.

 

Would you please clarify how the dashboard is broken after refreshing? 

 

Best Regards,
Qiuyun Yu 

Moderator
Status changed to: Needs Info
 
Frequent Visitor

Hi @v-qiuyu-msft , Please see below explanation. Thank you for your help with this issue.

 

 

 

Step 1: Pull last 5 days of data from Oracle db

Screenshot: Original data.Original data (last 5 days).jpgOriginal data from last 5 days (Oracle db)

Step 2: Pivot data with column as date now

Pivoted data (with date as column now).jpgUsing Transform - Pivot column function

Step 3: Dashboard with pivot table

Expected dashboard (24th April - creation date).jpgExpected dashboard - 24th April (Dashboard Creation date)

Step 4: Dashboard is broken, due to 20th April column missing in the dashboard (since it pulls last 5 days of data)

broken dashboard2.jpgBroken dashboard, 25th April not selected for dashboard

Question?:

Is there a way I can get last 5 days of data with Date as column without manual intervention?

 

End goal: Email this dashboard as status update to eveyone in my team daily with last 5 days of data. (Dashboard same as expected dashboard screenshot - step 3)

Moderator

Hi @c5966

 

I would suggest you delete the Pivoted Column step in Query Editor, then apply the change. Go to Report tab, add a matrix visual, put the CREATE_DATE column in the Columns bucket. In Visual Level Filter, set a filter condition like below: 

 

q4.PNG

 

Best Regards,
Qiuyun Yu 

Frequent Visitor

Hi @v-qiuyu-msft ,

 

Thank you so much for your help with my dashboard.

I tried this Matrix table and everything is working fine. except one condition when I am trying to add a column from other source. I can merge the data, but I am not able to see any null data THOUGH I have selected "Show items with no data"

Dashboard.jpgData without NULL

Can you help me get NULL data as well?

 

Appreciate your help.

 

Thank you

 

 

Frequent Visitor

I was able to see NULL values earlier with same tables with the pivot function and TABLE graph.

Moderator

Hi @c5966

 

It's hard to provide further help without looking into the issue on your side. I would suggest you create a support ticket to get dedicated support. 

 

Support Ticket.gif

 

Best Regards,
Qiuyun Yu