cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dading
Helper II
Helper II

Merging table base on time and category

Hi,

I am new in powerbi.

I m having trouble to merge these 2 tables, 

first i have t_category table and

EqmtStartTimeEndtimeCategory
E11/1/2020 7:301/1/2020 8:30A
E11/1/2020 8:301/1/2020 10:00B
E11/1/2020 10:001/1/2020 11:30C
E11/1/2020 11:301/1/2020 12:30D
E11/1/2020 12:301/1/2020 15:00A

 

 

second t_process table

EqmtXstartYstartZstart
E11/1/2020 7:301/1/2020 8:001/1/2020 9:00
E11/1/2020 9:301/1/2020 10:151/1/2020 11:00
E11/1/2020 11:351/1/2020 12:151/1/2020 12:45

 

i want to have a result like shows below

EqmtStartTimeEndtimeCategoryProcess
E11/1/2020 7:301/1/2020 8:00AXstart
E11/1/2020 8:001/1/2020 8:30AYstart
E11/1/2020 8:301/1/2020 9:00BYstart
E11/1/2020 9:001/1/2020 9:30BZstart
E11/1/2020 9:301/1/2020 10:00BXstart
E11/1/2020 10:001/1/2020 10:15CXstart
E11/1/2020 10:151/1/2020 11:00CYstart
E11/1/2020 11:001/1/2020 11:30CZstart
E11/1/2020 11:301/1/2020 11:35DZstart
E11/1/2020 11:351/1/2020 12:15DXstart
E11/1/2020 12:151/1/2020 12:30DYstart
E11/1/2020 12:301/1/2020 12:45AZstart
E11/1/2020 12:451/1/2020 15:00AZstart

can anyone help me to solve this?

i need to process it using M query

 

thanks in advance

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

wdx223_Daniel_0-1627531097617.png

 

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User
Super User

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

wdx223_Daniel_0-1627531097617.png

 

View solution in original post

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
Super User
Super User

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

Vera_33_0-1627525830199.png

 

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"

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors