Daily changing pivot table, dashboard error

[c5966]

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 1	Count (IDs)	Date
A	56	22-Apr
B	789	22-Apr
C	2312	22-Apr
D	3221	22-Apr
A		23-Apr
B	3454	23-Apr
C	231	23-Apr
D	3232	23-Apr
A	4856	24-Apr
B	232	24-Apr
C	353	24-Apr
D	1254	24-Apr
A		25-Apr
B	2134	25-Apr
C	342	25-Apr
D	354	25-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 1	22-Apr	23-Apr	24-Apr	25-Apr
A	56		4856
B	789	3454	232	2134
C	2312	231	353	342
D	3221	3232	1254	354

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.

[v-qiuyu-msft]

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 

[c5966]

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 from last 5 days (Oracle db)

Step 2: Pivot data with column as date now

Using Transform - Pivot column function

Step 3: Dashboard with pivot table

Expected 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 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)

[v-qiuyu-msft]

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: 

Best Regards,
Qiuyun Yu 

[c5966]

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"

Data without NULL

Can you help me get NULL data as well?

Appreciate your help.

Thank you

[c5966]

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

[v-qiuyu-msft]

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. 

Best Regards,
Qiuyun Yu