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

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

2 REPLIES 2
wdx223_Daniel
Super User II
Super User II

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

Vera_33
Solution Sage
Solution Sage

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors