Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
To report on things that are not there you need to use disconnected tables and/or crossjoins
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