Hi Team,
I have data up to Aug 20 at the moment. I would like to prepare the data for the month with null value. I can do that with SQL (case when). But I got my data from ERP cloud and I would like to do that in power query through Odata feed not going through SQL.
Please see attached file for my query so far and the expected query.
Thank you.
Regards,
Aye
Hi Aye,
I'm not sure what you are asking: 'prepare the data for month with null value'?
It's not clear where your source data comes from. You mention SQL and OData but your queries don't have any connections or information about such sources so I can't help with that aspect.
Your 'Required table' sheet shows a table with no data for fiscal periods 3 to 12. I'm assuming these periods are calendar months, you haven't said what they are.
So if you just want a report showing 12 fiscal periods including those periods without data, I'd include rows in your data for those periods/accounts/facilities you want to report on.
In your current query you are taking the source table and then pivoting the fiscal period column which goes against the ideal tabular data layout. You should leave it as a fiscal period column and just add more rows to the source data - see the sample on the MOTH sheet - I've added rows for periods 3 to 6.
PQ is for preparing data for reporting not creating the report. By pivoting that one column you are starting to prepare the report inside PQ.
With the data in tabular layout you can use a pivot table to create the report you want.
Regards
Phil
