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 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.
Thank you.
PQforNoDataMonth.xlsx (21.1 KB)
Regards,
Aye
Hello @ammu,
Thank You for posting your query onto the Forum.
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 -
I’m also attaching the Excel as well as the PBIX file of the working for the reference.
Hoping you find this useful. 
Thanks and Warm Regards,
Harsh
PQforNoDataMonth.xlsx (21.2 KB)
Power Query.pbix (17.8 KB)
Hi @ammu
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.
I attached the PBIX file for reference.
Let me know if this help you.
Cheers,
Yeriel.Power Query Fiscal Months.pbix (36.6 KB)
Hi Yeriel,
Thanks for the reply.
If I do that, and if I get more data for Sep, Oct and so on, it will not overwrite null data.
please let me know.
Thank you.
Regards,
Aye
Hi @ammu
Yes that´s true, it will not update if you add more data into the data model, this was just a quick snapshot.
I have a question, for this data model you want to create, the only data you have in the first tab right?
I´m asking because if you can use the third tab, @Harsh ´s answer would be great for the task.
Cheers,
Yeriel
Hello @ammu,
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 -
And then in Power BI you can have the analysis as shown below in the screenshot -
For solution by using a Formula. Below is the formula provided for the reference -
Total Values =
CALCULATE( SUM( 'Required table'[Values] ) ,
FILTER( 'Required table' ,
'Required table'[Values] > 0 ||
'Required table'[Values] <= 0 ) )
I’m also attaching the Excel as well as the PBIX file for the reference.
Hoping this meets your requirements.
Thanks and Warm Regards,
Harsh
PQforNoDataMonth.xlsx (21.2 KB)
Power Query.pbix (20.9 KB)
Hi @ammu
Okay, I made a new Data Model that will modify change every time you add the data, regardless of the data.
I had to create a Table with the 12 Fiscal Months thus I can add them later on into the Data Model.
Then I merged both tables into a new one to modify the Column Context (12 Months)
In Power BI would look something like this:
if you add new data to the DB will also change in Power BI.
Let me know if this is what you were looking for.
I attached the PBIX file for reference.
Cheers,
Yeriel
Power Query Fiscal Months 1.2.pbix (48.0 KB)
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
