Need assistance with Power Query

[jajatidev]

Hello All,

I'm looking for assistance to convert the following functions in M Language.

1. Ageing = IF([ReceiveDate]]=TODAY(),0,NETWORKDAYS([ReceiveDate]]+1,TODAY()))
2. Age Group =
   IF(AND([Ageing]>=0,[Ageing]<=5),"[0 - 5]",
   IF(AND([Ageing]>=6,[Ageing]<=10),"[6-10]",
   IF(AND([Ageing]>=11,[Ageing]<=20),"[11 - 20]",
   IF(AND([Ageing]>=21,[Ageing]<=30),"[21 - 30]",
   IF(AND([Ageing]>=31,[Ageing]<=60),"[ 31 - 60]",
   IF(AND([Ageing]>=61,[Ageing]<=90),"[ 61 - 90]",
   "[ > 90]"))))))
3. OrderWeek = [ReceiveDate]-WEEKDAY([ReceiveDate])+2
4. CRD_Month = IF(OR(ISBLANK([CustomerReqDate]),[CustomerReqDate]<=TODAY()+31),"CRDD Current","CRDD Future")
5. BlockCode =LET(x,TEXTSPLIT([UserStatus]," "),TEXTJOIN(" ",,XLOOKUP(x,UserStatusCode,UserStatusCode,"")))

For the 5 formula, please find the below table for reference.

SalesOrderNo	ProductNo	OrderQty	CompleteDelivery	UserStatus	BlockCode			UserStatusCode
112935380	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	INCO			INCO
113430548	A1A30A-KGJ	1	N	BLCA C2AP EOL2 NoMP REL SCAL	EOL2 SCAL			EOL2
113493710	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	INCO			RRDC
113512547	A1A30A-KGJ	1	N	C2AP EOL2 NoMP REL SCAL	EOL2 SCAL			SCAL
113543813	5RA32F-KGJ	1	N	C2AP MDID NoMP REL RRDC	RRDC			XBOP
113561139	A1A30A-KGJ	1	N	EOL2 MDID NoMP REL SCAL	EOL2 SCAL
113705782	A1A77A-KGJ	10	N	C2AP EOL2 MDID NoMP REL SCAL	EOL2 SCAL
113725103	N7L37A	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	INCO
113900946	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL	SCAL
113933893	3JA05AN-150	1	N	C2AP EOL2 NoMP REL	EOL2
113969730	A1A56A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL	EOL2 SCAL
113987750	5RA32F-KGJ	1	N	COM-GLA NoMP REL
114002150	2Z613F-KGJ	2	N	C2AP NoMP REL SCAL	SCAL
114002150	5RA32F-KGJ	1	N	COM-GLA NoMP REL
114003452	2Z600F-KGJ	5	N	C2AP NoMP REL SCAL	SCAL
114005180	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL	SCAL
114016353	A1A77A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL	EOL2 SCAL
110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL	SCAL
110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC	RRDC
110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL	SCAL
110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC	RRDC
110113433	Y3D03A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
110113433	Z7C07A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP	XBOP
110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP	XBOP
110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP
110433907	K5L35A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
110433907	Y3D03A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
110433907	Z7C07A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	SCAL XBOP

 

[jajatidev]

What I understand is you want to list the status codes right after REL status code, and it can be done by adding a new column by first splitting the codes as a list, find the position of the REL code, and finally remove the list items by using that position value. I used the final code I provided above as I am not sure what you'd like to do with the rest of the initial application logic. I believe you can use the method which I explained and added as the last step to the existing code and generate the logic if any.

If you have further questions about this new requirement, then it is best to start a new thread to explain it, so other Power Query users on the board can see your question to respond and you might receive better answers instead of mine only.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    UserStatusCodes = Excel.CurrentWorkbook(){[Name="UserStatusCodes"]}[Content][UserStatusCode],
    ChangeTypes = Table.TransformColumnTypes(Source,{{"ReceiveDate", type date}, {"CustomerReqDate", type date}, {"SalesOrderNo", type text}, {"ProductNo", type text}, {"OrderQty", Int64.Type}, {"CompleteDelivery", type text}, {"UserStatus", type text}}),
    Ageing = Table.AddColumn(ChangeTypes, "Ageing", each List.Sum({0} & List.Transform(List.Dates(Date.AddDays([ReceiveDate], 1), Duration.Days(Date.From(DateTime.LocalNow()) - [ReceiveDate]) , #duration(1, 0, 0, 0)), each if Date.DayOfWeek(_, Day.Monday) < 5 then 1 else 0))),
    AgeGroup = Table.AddColumn(Ageing, "AgeGroup",
                        each
                            if List.Contains({0..5}, [Ageing]) then "[0 - 5]"
                            else if List.Contains({6..10}, [Ageing]) then "[6 - 10]"
                            else if List.Contains({10..20}, [Ageing]) then "[11 - 20]"
                            else if List.Contains({21..30}, [Ageing]) then "[21 - 30]"
                            else if List.Contains({31..60}, [Ageing]) then "[31 - 60]"
                            else if List.Contains({61..90}, [Ageing]) then "[61 - 90]"
                            else "[ > 90]"
                ),
    OrderWeek = Table.AddColumn(AgeGroup, "OrderWeek",
                        each Date.WeekOfYear(Date.AddDays([ReceiveDate], 2 - Date.DayOfWeek([ReceiveDate])))
                ),
    CRD_Month = Table.AddColumn(OrderWeek, "CRD_Month",
                        each
                            if [CustomerReqDate] is null or [CustomerReqDate] <= Date.AddDays(Date.From(DateTime.LocalNow()), 31)
                            then "CRDD Current"
                            else "CRDD Future"
                ),
    BlockCode = Table.AddColumn(CRD_Month, "BlockCode",
                    each Text.Combine(
                            List.Intersect({Text.Split([UserStatus], " "), UserStatusCodes}),
                            " "
                    )
            ),
    AfterRel = Table.AddColumn(BlockCode, "AfterREL",
                    each
                        let
                            UserStatusCodes = Text.Split([UserStatus], " "),
                            RelPosition = List.PositionOf(UserStatusCodes, "REL"),
                            AfterRel = Text.Combine(List.RemoveFirstN(UserStatusCodes, RelPosition + 1), " ")
                        in
                            AfterRel
                    )
in
    AfterRel

Hello @smozgur,
I was finally able to try these codes and the results have been as expected. Thank you for all the guidance.

 

[smozgur]

Hello @smozgur,
I was finally able to try these codes and the results have been as expected. Thank you for all the guidance.

Glad to hear it helps. Thanks for the feedback.