Add Row if data missing from dataset

[AsNa_92]

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.

[Joe_Barry]

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 want

Hope this helps

Joe

[AsNa_92]

Hi @Joe_Barry 

thanks for replying .. but is there a way of adding a column or measure in the same table table instead of doing all that? 

I'm using oracle database table and it gets refreshed monthly.

[BIswajit_Das]

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...

[AsNa_92]

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

[BIswajit_Das · Accepted solution]

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

    )

)

OR

There's another way to get your desired result using M-Queries on transfer data but for that also you need to add another table .

OR

You can modify your used database Query.

Thanks & Regards...