Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am new in powerbi.
I m having trouble to merge these 2 tables,
first i have t_category table and
Eqmt | StartTime | Endtime | Category |
E1 | 1/1/2020 7:30 | 1/1/2020 8:30 | A |
E1 | 1/1/2020 8:30 | 1/1/2020 10:00 | B |
E1 | 1/1/2020 10:00 | 1/1/2020 11:30 | C |
E1 | 1/1/2020 11:30 | 1/1/2020 12:30 | D |
E1 | 1/1/2020 12:30 | 1/1/2020 15:00 | A |
second t_process table
Eqmt | Xstart | Ystart | Zstart |
E1 | 1/1/2020 7:30 | 1/1/2020 8:00 | 1/1/2020 9:00 |
E1 | 1/1/2020 9:30 | 1/1/2020 10:15 | 1/1/2020 11:00 |
E1 | 1/1/2020 11:35 | 1/1/2020 12:15 | 1/1/2020 12:45 |
i want to have a result like shows below
Eqmt | StartTime | Endtime | Category | Process |
E1 | 1/1/2020 7:30 | 1/1/2020 8:00 | A | Xstart |
E1 | 1/1/2020 8:00 | 1/1/2020 8:30 | A | Ystart |
E1 | 1/1/2020 8:30 | 1/1/2020 9:00 | B | Ystart |
E1 | 1/1/2020 9:00 | 1/1/2020 9:30 | B | Zstart |
E1 | 1/1/2020 9:30 | 1/1/2020 10:00 | B | Xstart |
E1 | 1/1/2020 10:00 | 1/1/2020 10:15 | C | Xstart |
E1 | 1/1/2020 10:15 | 1/1/2020 11:00 | C | Ystart |
E1 | 1/1/2020 11:00 | 1/1/2020 11:30 | C | Zstart |
E1 | 1/1/2020 11:30 | 1/1/2020 11:35 | D | Zstart |
E1 | 1/1/2020 11:35 | 1/1/2020 12:15 | D | Xstart |
E1 | 1/1/2020 12:15 | 1/1/2020 12:30 | D | Ystart |
E1 | 1/1/2020 12:30 | 1/1/2020 12:45 | A | Zstart |
E1 | 1/1/2020 12:45 | 1/1/2020 15:00 | A | Zstart |
can anyone help me to solve this?
i need to process it using M query
thanks in advance
Solved! Go to Solution.
let
t_process = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lEy1DfUNzIwMlAwtzI2QOZbWBmg8C1B/FgddG2WaNoMDawMTVEEDLFrBIoboyo0QtdpZGViqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Eqmt = _t, Xstart = _t, Ystart = _t, Zstart = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Xstart", type datetime}, {"Ystart", type datetime}, {"Zstart", type datetime}})
in
#"Changed Type",
t_category=let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lEy1DfUNzIwMlAwtzI2QOZbQPiOSrE66Eot0JQaGlgZgAScsKiFySEEDCG6nbEpNkQ32Qgi4IJNsRG6YlOIVUAnxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Eqmt = _t, StartTime = _t, Endtime = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"Endtime", type datetime}})
in
#"Changed Type",
Custom1 = Table.Buffer(Table.Group(Table.UnpivotOtherColumns(t_process,{"Eqmt"},"XYZ","Time"),"Eqmt",{"n",each _})),
Custom2 = #table(Table.ColumnNames(t_category)&{"Process"},List.TransformMany(Table.ToRows(t_category),each let a=Custom1{[Eqmt=_{0}]}?,b=Table.Distinct(Table.FillDown(Table.Sort(a[n][[Time],[XYZ]]&#table({"Time","XYZ"},{{_{1},null},{_{2},null}}),{"Time",{"XYZ",1}}),{"XYZ"})),c=Table.SelectRows(b,(x)=>x[Time]>=_{1} and x[Time]<=_{2} and x[XYZ]<>null) in List.RemoveLastN(List.Zip(Table.ToColumns(c)&{List.Skip(c[Time])}),1) ,(x,y)=>{x{0},y{0},y{2},x{3},y{1}}))
in
Custom2
let
t_process = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lEy1DfUNzIwMlAwtzI2QOZbWBmg8C1B/FgddG2WaNoMDawMTVEEDLFrBIoboyo0QtdpZGViqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Eqmt = _t, Xstart = _t, Ystart = _t, Zstart = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Xstart", type datetime}, {"Ystart", type datetime}, {"Zstart", type datetime}})
in
#"Changed Type",
t_category=let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lEy1DfUNzIwMlAwtzI2QOZbQPiOSrE66Eot0JQaGlgZgAScsKiFySEEDCG6nbEpNkQ32Qgi4IJNsRG6YlOIVUAnxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Eqmt = _t, StartTime = _t, Endtime = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"Endtime", type datetime}})
in
#"Changed Type",
Custom1 = Table.Buffer(Table.Group(Table.UnpivotOtherColumns(t_process,{"Eqmt"},"XYZ","Time"),"Eqmt",{"n",each _})),
Custom2 = #table(Table.ColumnNames(t_category)&{"Process"},List.TransformMany(Table.ToRows(t_category),each let a=Custom1{[Eqmt=_{0}]}?,b=Table.Distinct(Table.FillDown(Table.Sort(a[n][[Time],[XYZ]]&#table({"Time","XYZ"},{{_{1},null},{_{2},null}}),{"Time",{"XYZ",1}}),{"XYZ"})),c=Table.SelectRows(b,(x)=>x[Time]>=_{1} and x[Time]<=_{2} and x[XYZ]<>null) in List.RemoveLastN(List.Zip(Table.ToColumns(c)&{List.Skip(c[Time])}),1) ,(x,y)=>{x{0},y{0},y{2},x{3},y{1}}))
in
Custom2
thank you,. but could you please explain step by step, because im confused the meaning step of custom1 and 2
thanks in advance
Hi @dading
Here is one way, you can work from here, the 12:30-12:45 should be Ystart, I guess...I also hard coded one datetime value in Table2, your second process table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lEyMNQHIiMDIwMFA3MrYwM0IQuIkKNSrA4WDRYYGgwNrAxAQk5YNcBkkYUMIWY4Y9dgiGmDEUTIBbsGI0wNphBLgX6IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Eqmt = _t, StartTime = _t, Endtime = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Eqmt", type text}, {"StartTime", type datetime}, {"Endtime", type datetime}, {"Category", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([StartTime],Duration.TotalMinutes([Endtime]-[StartTime]),#duration(0,0,1,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Custom"}, Table2, {"Custom"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Process"}, {"Process"}),
#"Grouped Rows" = Table.Group(#"Expanded Table2", {"Eqmt", "Category", "Process"}, {{"all", each _, type table }}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Start", each List.Min([all][Custom])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "End", each List.Max([all][Custom])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"all"})
in
#"Removed Columns"
And this is reference table, called Table2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lEyMNQHIiMDIwMFA3MrYwM0IQsrA3QhS5BQrA4W/ZYY+g0NrAxN0YQMcekHyhijKzbC1G9kZWKqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Eqmt = _t, Xstart = _t, Ystart = _t, Zstart = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Eqmt", type text}, {"Xstart", type datetime}, {"Ystart", type datetime}, {"Zstart", type datetime}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Eqmt"}, "Process", "StartTime"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "EndTime", each try #"Added Index"[StartTime]{[Index]+1} otherwise #date(2020,1,1) & #time(18,0,0)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each List.DateTimes([StartTime],Duration.TotalMinutes([EndTime]-[StartTime]),#duration(0,0,1,0)) ),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom")
in
#"Expanded Custom"