Forum Discussion
= Table.NestedJoin
It sounds like all your want to do is take the max week for each Order and then call it a closed service order on the max week.
Just Group By Order, Operation Max Week, Operation All Rows. Expand and add custom column Week = Max Week.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnKCs0BiRnAxI7iYMVwMwTIBs5yBLFM4y0wpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Week = _t]), #"Grouped Rows" = Table.Group(Source, {"Order"}, {{"Max Week", each List.Max([Week]), type nullable text}, {"Rows", each _, type table [Order=nullable text, Week=nullable text]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Order"}), #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Columns", "Rows", {"Order", "Week"}, {"Order", "Week"}), #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Closed", each [Week] = [Max Week]) in #"Added Custom"Copy and paste entire code into blank query to see a full example.
Hi, so: good news is that I've not sintax error anymore. Bad news is that as a result I got a List of empty tables. Now let's try do get the same result in an other way:
If I have this query
let
#"Service Order Trend Elaborato" = #"Elabora dato SAP" (#"Service Order Trend RAW"),
#"Removed Columns" = Table.RemoveColumns(#"Service Order Trend Elaborato",{"Instrument Model", "Instrument Serial Number", "Functional Loc.", "Region", "Postal Code", "City", "Street", "Telephone", "Index", "Overdue days", "OverDue 5 days", "OverDue 2 days"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Week Number", "Source Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"Week", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.EndOfWeek(Date.AddWeeks(#date([Year], 1, 1), [Week]
), Day.Monday)),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "End Week Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"End Week Date", type date}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Over Due Days Weekly based", each Networkingdays([Created on], [End Week Date], null)),
#"Added Custom3" = Table.AddColumn(#"Invoked Custom Function", "Weekly New Service Order", each if Date.AddDays([Created on],5) >= [End Week Date] then true else false),
#"Added Custom1" = Table.AddColumn(#"Added Custom3", "Overdue >5days", each if [Over Due Days Weekly based] > 5 then true else false)
in
#"Added Custom1"
can I add a new column named "Closed Service Order" in the table that looking at "Week Number" Column will put value "TRUE" if one "Order" (in the column named "Order") contained in the current week is not present in the next week?
For example for week 50 in the new column named "Closed Service Order" will have a TRUE value when order in week 50 will not show in week 51
What is the formula I shoud add?
Maybe since, in this case, I've already and expanded whole table is easier to get the result I'm looking for.
I really appreciate your help.
Regards
Solution SageIt sounds like all your want to do is take the max week for each Order and then call it a closed service order on the max week.
Just Group By Order, Operation Max Week, Operation All Rows. Expand and add custom column Week = Max Week.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnKCs0BiRnAxI7iYMVwMwTIBs5yBLFM4y0wpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Week = _t]),
#"Grouped Rows" = Table.Group(Source, {"Order"}, {{"Max Week", each List.Max([Week]), type nullable text}, {"Rows", each _, type table [Order=nullable text, Week=nullable text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Order"}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Removed Columns", "Rows", {"Order", "Week"}, {"Order", "Week"}),
#"Added Custom" = Table.AddColumn(#"Expanded Rows", "Closed", each [Week] = [Max Week])
in
#"Added Custom"
Copy and paste entire code into blank query to see a full example.
Finally I see the light!!! Many Thanks!!!!
Now I need to figure out:
1) how to exclude the last week data since data are inconsistent because they have not a matching week yet;
2) MAX Week doesn't work for the new year. I mean after week 52 (2023) there's week 01 (2024) . I tried to put MAX on Source.Name that is (as example) 2023-W-52 but it doesn't work.
I need to think of it.
Thanks again for your help. I coundn't never achieve this result without your help.
Savino
- spinfuzer
Probably just concatenate year with week number. 202401 > 202352