I have data up to Aug 20 at the moment. I would like to prepare the data for the month with null value. I got my data from ERP cloud and I would like to do that in power query through Odata feed.
Please see attached file for my query so far and the expected query.
Well yes, when we try to unpivot the columns that has “Null” values the Power Query will unpivot only those columns that has values in it and remaining columns that has “Null” shall get eradicated. Below is the screenshot provided for the reference -
But what you can do is replace the “Null Values” with the zero’s (0) in your columns and then unpivot the data. Below are the screenshots provided for the reference -
Now, whenever your data gets updated in the future you just need to refresh it in the Power Query Editor and the data gets loaded. Below are the screenshots provided for the reference -
If you want to add the whole fiscal months in your Data Model, I´m thinking that you need to add them manually because you don´t have those month in your current data.
For this inquiry, I just basiccally add “Columns from Example” and paste it until I got the 12 Fiscal Months.
Well if you still want the values to be shown as “Null” and not the zero’s (0) then just add one more step in the file which I’ve provided. Initially, I’ve transformed the null values to 0 and then unpivoted the data. Now, after unpivoting the data again transform/replace the zero’s (0) with the null values you shall have the desired result. Below is the screenshots provided for the reference -
Below is the screenshot provided of the overall steps performed till now -
Still the file will remain refreshable. If I update the data in excel file and refresh it in the QueryEditor it incorporates the changes without breaking your query or showing any signs off error. Below are the screenshots provided for the reference -
Hi @ammu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Many thanks, Harsh and Yeriel.
With Power Query Fiscal Months 1.2 Pbix was perfect. that is the solution I required.
I am so sorry for replying you late.
Regards,
Aye