CODEWITHSUNDEEP
mysqlpowerbi
ramesh reddy
ramesh reddy

Reputation: 13

How to add missing value data in POWER BI / using SQL

I have a table containing below data which might have missing information. But in that case , it should appear and shown as zero.I tried cross join but its performance is bad since it will create large number of records.

material location week value
123       p1      wk11  9
123       p1      wk13  13
456       p2      wk11  8
456       p2      wk12  7

desired o/p: (for material 123). in short, all possible combination of material and plant and week will have value .

material location week value
123       p1      wk11  9
123       p1      wk12  0
123       p1      wk13  13
123       p2      wk11  0
123       p2      wk13  0
123       p2      wk12  0

Upvotes: 0

Views: 129

Answers (1)

Rick James
Rick James

Reputation: 142518

This is an example of LEFT JOIN and COALESCE().

SELECT  123 AS material,
        y.location,
        z.week,
        COALESCE(y.value, 0) AS value
    FROM table_with_list_of_all_wks AS z
    LEFT JOIN your_table AS y  ON y.week = z.week
    WHERE y.location = 'p1'

This requires pre-building a table with all possible "wk" values.

y.value will be NULL when a row (week) is missing; COALESCE turns that into 0.

Upvotes: 0

Related Questions