Need help with adding null values only on cells which doesn't have a value on another column

[Rod_rigo]

Help me out with the following: 

I'm currently working on creating a table which has data from different drilling rigs that operate in different zones, with 2 shifts (A & B), based on each shift daily report, my table layout would be:

Day	Month	Drill Rig	Zone	Shift
1	May	1	ZA	A
1	May	2	ZA	B
2	May	1	ZB	B
3	May	3	ZC	A
4	May	3	ZB	A
4	May	1	ZA	A
4	May	1	ZA	B

The problem is I also want to have the information on what date, zone and shift each drill rig was NOT operating in, but I can't go and add empty spaces for each date, zone and shift combination for each rig, it would be too much work. ¿Is there a way i can achieve this in powerquery?, my main objective is to create a matrix visual for powerBI, like this one:

Thanks for any help I can get.

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins

[dufoq3 · Accepted solution]

Hi @Rod_rigo, regarding you are asking in Power Query forum I'm providing Power Query solution. Just remember that you have to use unique column name for each column - that's why I've used [may-01-A] etc.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNrASSIFaUI5BwVIrVQZYwgkk4gSWMUHU4wSWM4RIgVpQz3CgTVAknLBJolmORANoRCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Month = _t, #"Drill Rig" = _t, Zone = _t, Shift = _t]),
    AddedPrefix_DrillRig = Table.TransformColumns(Source, {{"Drill Rig", each "Drill" & Text.From(_), type text}}),
    Ad_DateHelper = Table.AddColumn(AddedPrefix_DrillRig, "DateHelper", each Text.Lower([Month]) & "-" & Text.PadStart(Text.From([Day]), 2, "0"), type text),
    Ad_Date = Table.AddColumn(Ad_DateHelper, "Date", each [DateHelper] & "-" & [Shift], type text),
    Transformed = List.Buffer(
        [ shifts = List.Distinct(Ad_Date[Shift]),
          dates = List.Distinct(Ad_Date[DateHelper]),
          datesTransformed = List.Sort(List.Combine(List.Transform(shifts, (x)=> List.Transform(dates, (y)=> y & "-" & x))))
        ][datesTransformed] ),
    GroupedRows = Table.Group(Ad_Date, {"Zone"}, {{"All", each 
        List.Accumulate(
            Transformed,
            #table(type table[Zone=text], {{[Zone]{0}}}),
            (s,c)=> Table.AddColumn(s, c, (x)=> Table.SelectRows(_, (y)=> y[Date] = c)[Drill Rig]{0}?, type text)
        ), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll