Merging table base on time and category

[dading]

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

[wdx223_Daniel · Accepted 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

[dading]

thank you,. but could you please explain step by step, because im confused the meaning step of custom1 and 2

thanks in advance

[Vera_33]

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"