Forum Discussion
Need help with adding null values only on cells which doesn't have a value on another column
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.
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
2 Replies
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 CombinedAllTo report on things that are not there you need to use disconnected tables and/or crossjoins
