Forum Discussion
Add Row if data missing from dataset
Hi Guys,
How Can I add row (data) if the data from source is missing?
For Instance, I have category with value (Red=50, Green=20, Black=5) in 2024/09/01
But in 2024/09/02 the data is (Red=30, Black=80)
How can I add data for Green with 0 if there is no data for it? Condition which is static for all days in future.
Hello AsNa_92
As per your requirement you need to create a calculated table cause here you're trying to add a row with the alternative result for category date wise.
So Try the below DAX to create a calculated table;RESULT_TABLE =ADDCOLUMNS(CROSSJOIN(DISTINCT('TABLE1'[Date]),DISTINCT('TABLE1'[Category])),"Value",COALESCE(LOOKUPVALUE('TABLE1'[Value], 'TABLE1'[Date], [Date], 'TABLE1'[Category], [Category]),0))ORThere's another way to get your desired result using M-Queries on transfer data but for that also you need to add another table .ORYou can modify your used database Query.Thanks & Regards...
5 Replies
Hello AsNa_92
It would be better if you share some demo data in order to check the formats and to understand your requirement better.
And as per the mentioned above example;1 - If you are loading the data using database queries then you can modify the column in the query and load the desired data.
or
2 - You can also create a calculated column with some conditions to get your required result column.
Thanks & Regards...Hi BIswajit_Das
Thanks for your reply, if I want to add column with condition how can i apply that?
Since I'm using oracle database table which refresh monthly.
Sample Data:
Date Category Value 2024/09/01 Red 50 2024/09/01 Yellow 40 2024/09/01 Black 5 2024/10/01 Red 90 2024/10/01 Black 30 Output:
Date Category Value 2024/09/01 Red 50 2024/09/01 Yellow 40 2024/09/01 Black 5 2024/10/01 Red 90 2024/10/01 Black 30 2024/10/01 Yellow 0 Hello AsNa_92
As per your requirement you need to create a calculated table cause here you're trying to add a row with the alternative result for category date wise.
So Try the below DAX to create a calculated table;RESULT_TABLE =ADDCOLUMNS(CROSSJOIN(DISTINCT('TABLE1'[Date]),DISTINCT('TABLE1'[Category])),"Value",COALESCE(LOOKUPVALUE('TABLE1'[Value], 'TABLE1'[Date], [Date], 'TABLE1'[Category], [Category]),0))ORThere's another way to get your desired result using M-Queries on transfer data but for that also you need to add another table .ORYou can modify your used database Query.Thanks & Regards...
Hi AsNa_92
There are various ways of doing it depending on your source, but in Power Query, I would create a new table that has the same columns as the existing one or at least the columns that you need. You can do this by using the Enter Data function in the Ribbon. Make sure that the column names match that of the other table, watch out for lower & upper case. Fill in the row with Green 0 and what ever the other columns you have.
When you are finished go to the original table and choose Append table and choose the table you just created. It will now be part of that table.
Regarding that it's static for all days in the future, this depends on the columns you have in the original table.
In DAX you could create a running total measure that will populate a visual or a table based on the date range you have chosen.Running Total = VAR _MaxDate = MAX('Calendar'[Date]) RETURN CALCULATE( SUM('Table'[Amount]), ///Is your table showing a culmative value? Then use MAX instead of SUM FILTER( ALL('Calendar'), 'Calendar'[Date] <= _MaxDate )
Add this measure to a visual along with the columns from your calendar table that you want to use for showing the date and the colour column from your table and you will egt the result you wantHope this helps
Joe
