Need assistance with Power Query

jajatidev · Aug 2, 2023

Hello All,

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

Ageing = IF([ReceiveDate]]=TODAY(),0,NETWORKDAYS([ReceiveDate]]+1,TODAY()))
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]"))))))
OrderWeek = [ReceiveDate]-WEEKDAY([ReceiveDate])+2
CRD_Month = IF(OR(ISBLANK([CustomerReqDate]),[CustomerReqDate]<=TODAY()+31),"CRDD Current","CRDD Future")
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

smozgur · Aug 2, 2023

Date columns, CustomerReqDate and ReceiveDate, are missing in your sample data table. So, I have to use sample dates in my source data below.
The current OrderWeek formula doesn't make sense. I assumed you wanted to get the week of the year of the received date.

Here is the base data.
The first table is named as "Data". The second table is named UserStatusCodes contains one column named "UserStatusCode" as in the original sample.



ReceiveDate	CustomerReqDate	SalesOrderNo	ProductNo	OrderQty	CompleteDelivery	UserStatus	UserStatusCode
6/22/2023	9/15/2023	112935380	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	INCO
7/3/2023	7/7/2023	113430548	A1A30A-KGJ	1	N	BLCA C2AP EOL2 NoMP REL SCAL	EOL2
7/4/2023	7/8/2023	113493710	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	RRDC
7/5/2023	7/9/2023	113512547	A1A30A-KGJ	1	N	C2AP EOL2 NoMP REL SCAL	SCAL
7/6/2023	7/10/2023	113543813	5RA32F-KGJ	1	N	C2AP MDID NoMP REL RRDC	XBOP
7/7/2023	7/11/2023	113561139	A1A30A-KGJ	1	N	EOL2 MDID NoMP REL SCAL
7/8/2023	7/12/2023	113705782	A1A77A-KGJ	10	N	C2AP EOL2 MDID NoMP REL SCAL
7/9/2023	7/13/2023	113725103	N7L37A	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL
7/10/2023	7/14/2023	113900946	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL
7/11/2023	7/15/2023	113933893	3JA05AN-150	1	N	C2AP EOL2 NoMP REL
7/12/2023	7/16/2023	113969730	A1A56A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL
7/13/2023	7/17/2023	113987750	5RA32F-KGJ	1	N	COM-GLA NoMP REL
7/14/2023	7/18/2023	114002150	2Z613F-KGJ	2	N	C2AP NoMP REL SCAL
7/15/2023	7/19/2023	114002150	5RA32F-KGJ	1	N	COM-GLA NoMP REL
7/16/2023	7/20/2023	114003452	2Z600F-KGJ	5	N	C2AP NoMP REL SCAL
7/17/2023	7/21/2023	114005180	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL
7/18/2023	7/22/2023	114016353	A1A77A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL
7/19/2023	7/23/2023	110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL
7/20/2023	7/24/2023	110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC
7/21/2023	7/25/2023	110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL
7/22/2023	7/26/2023	110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC
7/23/2023	7/27/2023	110113433	Y3D03A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
7/24/2023	7/28/2023	110113433	Z7C07A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
7/25/2023	7/29/2023	110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP
7/26/2023	7/30/2023	110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
7/27/2023	7/31/2023	110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP
7/28/2023	8/1/2023	110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
7/29/2023	8/2/2023	110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP
7/30/2023	8/3/2023	110433907	K5L35A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
7/31/2023	8/4/2023	110433907	Y3D03A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
8/1/2023	8/5/2023	110433907	Z7C07A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
8/2/2023	8/6/2023	110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP

I am not sure about the efficiency of my way of finding the NetworkDays count, but here is my attempt.

Power Query:

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
                        let
                            startDate = Date.AddDays([ReceiveDate], 1),
                            endDate = Date.From(DateTime.LocalNow()),
                            Result = List.Accumulate(
                                {0..Date.DayOfYear(endDate) - Date.DayOfYear(startDate)},
                                0,
                                (state, current) => if Date.DayOfWeek(Date.AddDays(startDate, current), Day.Sunday) < 1 then state else state + 1
                            )
                        in
                            Result
            ),
    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}),
                            " "
                    )
            )
in
    BlockCode

And the result:



ReceiveDate	CustomerReqDate	SalesOrderNo	ProductNo	OrderQty	CompleteDelivery	UserStatus	Ageing	AgeGroup	OrderWeek	CRD_Month	BlockCode
6/22/2023	9/15/2023	112935380	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	35	[31 - 60]	25	CRDD Future	INCO
7/3/2023	7/7/2023	113430548	A1A30A-KGJ	1	N	BLCA C2AP EOL2 NoMP REL SCAL	26	[21 - 30]	27	CRDD Current	EOL2 SCAL
7/4/2023	7/8/2023	113493710	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	25	[21 - 30]	27	CRDD Current	INCO
7/5/2023	7/9/2023	113512547	A1A30A-KGJ	1	N	C2AP EOL2 NoMP REL SCAL	24	[21 - 30]	27	CRDD Current	EOL2 SCAL
7/6/2023	7/10/2023	113543813	5RA32F-KGJ	1	N	C2AP MDID NoMP REL RRDC	23	[21 - 30]	27	CRDD Current	RRDC
7/7/2023	7/11/2023	113561139	A1A30A-KGJ	1	N	EOL2 MDID NoMP REL SCAL	22	[21 - 30]	27	CRDD Current	EOL2 SCAL
7/8/2023	7/12/2023	113705782	A1A77A-KGJ	10	N	C2AP EOL2 MDID NoMP REL SCAL	21	[21 - 30]	27	CRDD Current	EOL2 SCAL
7/9/2023	7/13/2023	113725103	N7L37A	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	21	[21 - 30]	28	CRDD Current	INCO
7/10/2023	7/14/2023	113900946	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL	20	[11 - 20]	28	CRDD Current	SCAL
7/11/2023	7/15/2023	113933893	3JA05AN-150	1	N	C2AP EOL2 NoMP REL	19	[11 - 20]	28	CRDD Current	EOL2
7/12/2023	7/16/2023	113969730	A1A56A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL	18	[11 - 20]	28	CRDD Current	EOL2 SCAL
7/13/2023	7/17/2023	113987750	5RA32F-KGJ	1	N	COM-GLA NoMP REL	17	[11 - 20]	28	CRDD Current
7/14/2023	7/18/2023	114002150	2Z613F-KGJ	2	N	C2AP NoMP REL SCAL	16	[11 - 20]	28	CRDD Current	SCAL
7/15/2023	7/19/2023	114002150	5RA32F-KGJ	1	N	COM-GLA NoMP REL	15	[11 - 20]	28	CRDD Current
7/16/2023	7/20/2023	114003452	2Z600F-KGJ	5	N	C2AP NoMP REL SCAL	15	[11 - 20]	29	CRDD Current	SCAL
7/17/2023	7/21/2023	114005180	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL	14	[11 - 20]	29	CRDD Current	SCAL
7/18/2023	7/22/2023	114016353	A1A77A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL	13	[11 - 20]	29	CRDD Current	EOL2 SCAL
7/19/2023	7/23/2023	110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL	12	[11 - 20]	29	CRDD Current	SCAL
7/20/2023	7/24/2023	110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC	11	[11 - 20]	29	CRDD Current	RRDC
7/21/2023	7/25/2023	110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL	10	[6 - 10]	29	CRDD Current	SCAL
7/22/2023	7/26/2023	110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC	9	[6 - 10]	29	CRDD Current	RRDC
7/23/2023	7/27/2023	110113433	Y3D03A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	9	[6 - 10]	30	CRDD Current
7/24/2023	7/28/2023	110113433	Z7C07A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	8	[6 - 10]	30	CRDD Current
7/25/2023	7/29/2023	110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP	7	[6 - 10]	30	CRDD Current	XBOP
7/26/2023	7/30/2023	110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL	6	[6 - 10]	30	CRDD Current
7/27/2023	7/31/2023	110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP	5	[0 - 5]	30	CRDD Current	XBOP
7/28/2023	8/1/2023	110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL	4	[0 - 5]	30	CRDD Current
7/29/2023	8/2/2023	110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	3	[0 - 5]	30	CRDD Current	SCAL XBOP
7/30/2023	8/3/2023	110433907	K5L35A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	3	[0 - 5]	31	CRDD Current
7/31/2023	8/4/2023	110433907	Y3D03A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	2	[0 - 5]	31	CRDD Current
8/1/2023	8/5/2023	110433907	Z7C07A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	1	[0 - 5]	31	CRDD Current
8/2/2023	8/6/2023	110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	0	[0 - 5]	31	CRDD Current	SCAL XBOP

smozgur · Aug 2, 2023

I just realized that there is a week-of-day mistake in the previous code. The Ageing step should be as follows (only the colored section is changed):

Rich (BB code):

   Ageing = Table.AddColumn(ChangeTypes, "Ageing", 
                    each
                        let
                            startDate = Date.AddDays([ReceiveDate], 1),
                            endDate = Date.From(DateTime.LocalNow()),
                            Result = List.Accumulate(
                                {0..Date.DayOfYear(endDate) - Date.DayOfYear(startDate)},
                                0,
                                (state, current) => if Date.DayOfWeek(Date.AddDays(startDate, current), Day.Monday) > 4 then state else state + 1
                            )
                        in
                            Result
            ),

Also, the following modified version is not using List.Accumulate() function, and it should be much more efficient:

Power Query:

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}),
                            " "
                    )
            )
in
    BlockCode

And the result:



ReceiveDate	CustomerReqDate	SalesOrderNo	ProductNo	OrderQty	CompleteDelivery	UserStatus	Ageing	AgeGroup	OrderWeek	CRD_Month	BlockCode
6/22/2023	9/15/2023	112935380	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	29	[21 - 30]	25	CRDD Future	INCO
7/3/2023	7/7/2023	113430548	A1A30A-KGJ	1	N	BLCA C2AP EOL2 NoMP REL SCAL	22	[21 - 30]	27	CRDD Current	EOL2 SCAL
7/4/2023	7/8/2023	113493710	1NR73A-K1H	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	21	[21 - 30]	27	CRDD Current	INCO
7/5/2023	7/9/2023	113512547	A1A30A-KGJ	1	N	C2AP EOL2 NoMP REL SCAL	20	[11 - 20]	27	CRDD Current	EOL2 SCAL
7/6/2023	7/10/2023	113543813	5RA32F-KGJ	1	N	C2AP MDID NoMP REL RRDC	19	[11 - 20]	27	CRDD Current	RRDC
7/7/2023	7/11/2023	113561139	A1A30A-KGJ	1	N	EOL2 MDID NoMP REL SCAL	18	[11 - 20]	27	CRDD Current	EOL2 SCAL
7/8/2023	7/12/2023	113705782	A1A77A-KGJ	10	N	C2AP EOL2 MDID NoMP REL SCAL	18	[11 - 20]	27	CRDD Current	EOL2 SCAL
7/9/2023	7/13/2023	113725103	N7L37A	1	N	C2AP INC-VBAP-CEPOK INCO MDID NoMP REL	18	[11 - 20]	28	CRDD Current	INCO
7/10/2023	7/14/2023	113900946	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL	17	[11 - 20]	28	CRDD Current	SCAL
7/11/2023	7/15/2023	113933893	3JA05AN-150	1	N	C2AP EOL2 NoMP REL	16	[11 - 20]	28	CRDD Current	EOL2
7/12/2023	7/16/2023	113969730	A1A56A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL	15	[11 - 20]	28	CRDD Current	EOL2 SCAL
7/13/2023	7/17/2023	113987750	5RA32F-KGJ	1	N	COM-GLA NoMP REL	14	[11 - 20]	28	CRDD Current
7/14/2023	7/18/2023	114002150	2Z613F-KGJ	2	N	C2AP NoMP REL SCAL	13	[11 - 20]	28	CRDD Current	SCAL
7/15/2023	7/19/2023	114002150	5RA32F-KGJ	1	N	COM-GLA NoMP REL	13	[11 - 20]	28	CRDD Current
7/16/2023	7/20/2023	114003452	2Z600F-KGJ	5	N	C2AP NoMP REL SCAL	13	[11 - 20]	29	CRDD Current	SCAL
7/17/2023	7/21/2023	114005180	2Z600F-KGJ	1	N	C2AP NoMP REL SCAL	12	[11 - 20]	29	CRDD Current	SCAL
7/18/2023	7/22/2023	114016353	A1A77A-KGJ	1	N	C2AP EOL2 MDID NoMP REL SCAL	11	[11 - 20]	29	CRDD Current	EOL2 SCAL
7/19/2023	7/23/2023	110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL	10	[6 - 10]	29	CRDD Current	SCAL
7/20/2023	7/24/2023	110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC	9	[6 - 10]	29	CRDD Current	RRDC
7/21/2023	7/25/2023	110113433	3GZ25A-KGJ	1	Y	BLKD C2AP COM-GLA NoMP REL SCAL	8	[6 - 10]	29	CRDD Current	SCAL
7/22/2023	7/26/2023	110113433	K5L35A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC	8	[6 - 10]	29	CRDD Current	RRDC
7/23/2023	7/27/2023	110113433	Y3D03A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	8	[6 - 10]	30	CRDD Current
7/24/2023	7/28/2023	110113433	Z7C07A	1	Y	BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	7	[6 - 10]	30	CRDD Current
7/25/2023	7/29/2023	110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP	6	[6 - 10]	30	CRDD Current	XBOP
7/26/2023	7/30/2023	110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL	5	[0 - 5]	30	CRDD Current
7/27/2023	7/31/2023	110113433	3PZ35A-KGJ	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP	4	[0 - 5]	30	CRDD Current	XBOP
7/28/2023	8/1/2023	110113433	D7P27A	1	Y	C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL	3	[0 - 5]	30	CRDD Current
7/29/2023	8/2/2023	110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	3	[0 - 5]	30	CRDD Current	SCAL XBOP
7/30/2023	8/3/2023	110433907	K5L35A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	3	[0 - 5]	31	CRDD Current
7/31/2023	8/4/2023	110433907	Y3D03A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	2	[0 - 5]	31	CRDD Current
8/1/2023	8/5/2023	110433907	Z7C07A	1	Y	BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL	1	[0 - 5]	31	CRDD Current
8/2/2023	8/6/2023	110433907	3GZ25A-KGJ	1	Y	BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP	0	[0 - 5]	31	CRDD Current	SCAL XBOP

jajatidev · Aug 2, 2023

smozgur said:
I just realized that there is a week-of-day mistake in the previous code. The Ageing step should be as follows (only the colored section is changed):

Rich (BB code):

Ageing = Table.AddColumn(ChangeTypes, "Ageing", each let startDate = Date.AddDays([ReceiveDate], 1), endDate = Date.From(DateTime.LocalNow()), Result = List.Accumulate( {0..Date.DayOfYear(endDate) - Date.DayOfYear(startDate)}, 0, (state, current) => if Date.DayOfWeek(Date.AddDays(startDate, current), Day.Monday) > 4 then state else state + 1 ) in Result ),

Also, the following modified version is not using List.Accumulate() function, and it should be much more efficient:

Power Query:

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}), " " ) ) in BlockCode

And the result:

ReceiveDate CustomerReqDate SalesOrderNo ProductNo OrderQty CompleteDelivery UserStatus Ageing AgeGroup OrderWeek CRD_Month BlockCode
6/22/2023 9/15/2023 112935380 1NR73A-K1H 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 29 [21 - 30] 25 CRDD Future INCO
7/3/2023 7/7/2023 113430548 A1A30A-KGJ 1 N BLCA C2AP EOL2 NoMP REL SCAL 22 [21 - 30] 27 CRDD Current EOL2 SCAL
7/4/2023 7/8/2023 113493710 1NR73A-K1H 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 21 [21 - 30] 27 CRDD Current INCO
7/5/2023 7/9/2023 113512547 A1A30A-KGJ 1 N C2AP EOL2 NoMP REL SCAL 20 [11 - 20] 27 CRDD Current EOL2 SCAL
7/6/2023 7/10/2023 113543813 5RA32F-KGJ 1 N C2AP MDID NoMP REL RRDC 19 [11 - 20] 27 CRDD Current RRDC
7/7/2023 7/11/2023 113561139 A1A30A-KGJ 1 N EOL2 MDID NoMP REL SCAL 18 [11 - 20] 27 CRDD Current EOL2 SCAL
7/8/2023 7/12/2023 113705782 A1A77A-KGJ 10 N C2AP EOL2 MDID NoMP REL SCAL 18 [11 - 20] 27 CRDD Current EOL2 SCAL
7/9/2023 7/13/2023 113725103 N7L37A 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 18 [11 - 20] 28 CRDD Current INCO
7/10/2023 7/14/2023 113900946 2Z600F-KGJ 1 N C2AP NoMP REL SCAL 17 [11 - 20] 28 CRDD Current SCAL
7/11/2023 7/15/2023 113933893 3JA05AN-150 1 N C2AP EOL2 NoMP REL 16 [11 - 20] 28 CRDD Current EOL2
7/12/2023 7/16/2023 113969730 A1A56A-KGJ 1 N C2AP EOL2 MDID NoMP REL SCAL 15 [11 - 20] 28 CRDD Current EOL2 SCAL
7/13/2023 7/17/2023 113987750 5RA32F-KGJ 1 N COM-GLA NoMP REL 14 [11 - 20] 28 CRDD Current
7/14/2023 7/18/2023 114002150 2Z613F-KGJ 2 N C2AP NoMP REL SCAL 13 [11 - 20] 28 CRDD Current SCAL
7/15/2023 7/19/2023 114002150 5RA32F-KGJ 1 N COM-GLA NoMP REL 13 [11 - 20] 28 CRDD Current
7/16/2023 7/20/2023 114003452 2Z600F-KGJ 5 N C2AP NoMP REL SCAL 13 [11 - 20] 29 CRDD Current SCAL
7/17/2023 7/21/2023 114005180 2Z600F-KGJ 1 N C2AP NoMP REL SCAL 12 [11 - 20] 29 CRDD Current SCAL
7/18/2023 7/22/2023 114016353 A1A77A-KGJ 1 N C2AP EOL2 MDID NoMP REL SCAL 11 [11 - 20] 29 CRDD Current EOL2 SCAL
7/19/2023 7/23/2023 110113433 3GZ25A-KGJ 1 Y BLKD C2AP COM-GLA NoMP REL SCAL 10 [6 - 10] 29 CRDD Current SCAL
7/20/2023 7/24/2023 110113433 K5L35A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC 9 [6 - 10] 29 CRDD Current RRDC
7/21/2023 7/25/2023 110113433 3GZ25A-KGJ 1 Y BLKD C2AP COM-GLA NoMP REL SCAL 8 [6 - 10] 29 CRDD Current SCAL
7/22/2023 7/26/2023 110113433 K5L35A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC 8 [6 - 10] 29 CRDD Current RRDC
7/23/2023 7/27/2023 110113433 Y3D03A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 8 [6 - 10] 30 CRDD Current
7/24/2023 7/28/2023 110113433 Z7C07A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 7 [6 - 10] 30 CRDD Current
7/25/2023 7/29/2023 110113433 3PZ35A-KGJ 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP 6 [6 - 10] 30 CRDD Current XBOP
7/26/2023 7/30/2023 110113433 D7P27A 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL 5 [0 - 5] 30 CRDD Current
7/27/2023 7/31/2023 110113433 3PZ35A-KGJ 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP 4 [0 - 5] 30 CRDD Current XBOP
7/28/2023 8/1/2023 110113433 D7P27A 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL 3 [0 - 5] 30 CRDD Current
7/29/2023 8/2/2023 110433907 3GZ25A-KGJ 1 Y BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP 3 [0 - 5] 30 CRDD Current SCAL XBOP
7/30/2023 8/3/2023 110433907 K5L35A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 3 [0 - 5] 31 CRDD Current
7/31/2023 8/4/2023 110433907 Y3D03A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 2 [0 - 5] 31 CRDD Current
8/1/2023 8/5/2023 110433907 Z7C07A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 1 [0 - 5] 31 CRDD Current
8/2/2023 8/6/2023 110433907 3GZ25A-KGJ 1 Y BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP 0 [0 - 5] 31 CRDD Current SCAL XBOP

Thank you. Please allow me time to apply the codes as I'm a beginner and just started learning Power BI

jajatidev · Aug 14, 2023

smozgur said:
I just realized that there is a week-of-day mistake in the previous code. The Ageing step should be as follows (only the colored section is changed):

Rich (BB code):

Ageing = Table.AddColumn(ChangeTypes, "Ageing", each let startDate = Date.AddDays([ReceiveDate], 1), endDate = Date.From(DateTime.LocalNow()), Result = List.Accumulate( {0..Date.DayOfYear(endDate) - Date.DayOfYear(startDate)}, 0, (state, current) => if Date.DayOfWeek(Date.AddDays(startDate, current), Day.Monday) > 4 then state else state + 1 ) in Result ),

Also, the following modified version is not using List.Accumulate() function, and it should be much more efficient:

Power Query:

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}), " " ) ) in BlockCode

And the result:

ReceiveDate CustomerReqDate SalesOrderNo ProductNo OrderQty CompleteDelivery UserStatus Ageing AgeGroup OrderWeek CRD_Month BlockCode
6/22/2023 9/15/2023 112935380 1NR73A-K1H 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 29 [21 - 30] 25 CRDD Future INCO
7/3/2023 7/7/2023 113430548 A1A30A-KGJ 1 N BLCA C2AP EOL2 NoMP REL SCAL 22 [21 - 30] 27 CRDD Current EOL2 SCAL
7/4/2023 7/8/2023 113493710 1NR73A-K1H 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 21 [21 - 30] 27 CRDD Current INCO
7/5/2023 7/9/2023 113512547 A1A30A-KGJ 1 N C2AP EOL2 NoMP REL SCAL 20 [11 - 20] 27 CRDD Current EOL2 SCAL
7/6/2023 7/10/2023 113543813 5RA32F-KGJ 1 N C2AP MDID NoMP REL RRDC 19 [11 - 20] 27 CRDD Current RRDC
7/7/2023 7/11/2023 113561139 A1A30A-KGJ 1 N EOL2 MDID NoMP REL SCAL 18 [11 - 20] 27 CRDD Current EOL2 SCAL
7/8/2023 7/12/2023 113705782 A1A77A-KGJ 10 N C2AP EOL2 MDID NoMP REL SCAL 18 [11 - 20] 27 CRDD Current EOL2 SCAL
7/9/2023 7/13/2023 113725103 N7L37A 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 18 [11 - 20] 28 CRDD Current INCO
7/10/2023 7/14/2023 113900946 2Z600F-KGJ 1 N C2AP NoMP REL SCAL 17 [11 - 20] 28 CRDD Current SCAL
7/11/2023 7/15/2023 113933893 3JA05AN-150 1 N C2AP EOL2 NoMP REL 16 [11 - 20] 28 CRDD Current EOL2
7/12/2023 7/16/2023 113969730 A1A56A-KGJ 1 N C2AP EOL2 MDID NoMP REL SCAL 15 [11 - 20] 28 CRDD Current EOL2 SCAL
7/13/2023 7/17/2023 113987750 5RA32F-KGJ 1 N COM-GLA NoMP REL 14 [11 - 20] 28 CRDD Current
7/14/2023 7/18/2023 114002150 2Z613F-KGJ 2 N C2AP NoMP REL SCAL 13 [11 - 20] 28 CRDD Current SCAL
7/15/2023 7/19/2023 114002150 5RA32F-KGJ 1 N COM-GLA NoMP REL 13 [11 - 20] 28 CRDD Current
7/16/2023 7/20/2023 114003452 2Z600F-KGJ 5 N C2AP NoMP REL SCAL 13 [11 - 20] 29 CRDD Current SCAL
7/17/2023 7/21/2023 114005180 2Z600F-KGJ 1 N C2AP NoMP REL SCAL 12 [11 - 20] 29 CRDD Current SCAL
7/18/2023 7/22/2023 114016353 A1A77A-KGJ 1 N C2AP EOL2 MDID NoMP REL SCAL 11 [11 - 20] 29 CRDD Current EOL2 SCAL
7/19/2023 7/23/2023 110113433 3GZ25A-KGJ 1 Y BLKD C2AP COM-GLA NoMP REL SCAL 10 [6 - 10] 29 CRDD Current SCAL
7/20/2023 7/24/2023 110113433 K5L35A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC 9 [6 - 10] 29 CRDD Current RRDC
7/21/2023 7/25/2023 110113433 3GZ25A-KGJ 1 Y BLKD C2AP COM-GLA NoMP REL SCAL 8 [6 - 10] 29 CRDD Current SCAL
7/22/2023 7/26/2023 110113433 K5L35A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC 8 [6 - 10] 29 CRDD Current RRDC
7/23/2023 7/27/2023 110113433 Y3D03A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 8 [6 - 10] 30 CRDD Current
7/24/2023 7/28/2023 110113433 Z7C07A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 7 [6 - 10] 30 CRDD Current
7/25/2023 7/29/2023 110113433 3PZ35A-KGJ 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP 6 [6 - 10] 30 CRDD Current XBOP
7/26/2023 7/30/2023 110113433 D7P27A 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL 5 [0 - 5] 30 CRDD Current
7/27/2023 7/31/2023 110113433 3PZ35A-KGJ 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP 4 [0 - 5] 30 CRDD Current XBOP
7/28/2023 8/1/2023 110113433 D7P27A 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL 3 [0 - 5] 30 CRDD Current
7/29/2023 8/2/2023 110433907 3GZ25A-KGJ 1 Y BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP 3 [0 - 5] 30 CRDD Current SCAL XBOP
7/30/2023 8/3/2023 110433907 K5L35A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 3 [0 - 5] 31 CRDD Current
7/31/2023 8/4/2023 110433907 Y3D03A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 2 [0 - 5] 31 CRDD Current
8/1/2023 8/5/2023 110433907 Z7C07A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 1 [0 - 5] 31 CRDD Current
8/2/2023 8/6/2023 110433907 3GZ25A-KGJ 1 Y BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP 0 [0 - 5] 31 CRDD Current SCAL XBOP

Thanks. I was able to understand and apply the queries for desired results. Still, a long way to go.

smozgur · Aug 14, 2023

jajatidev said:
Thanks. I was able to understand and apply the queries for desired results. Still, a long way to go.

You're welcome.
I am also glad to hear that you understand the code as there are many useful functions and methods in it. Although I posted the second code to make it more efficient without using List.Accumulate, you will find the List.Accumulate function is also a very powerful function once you understand how it works.

Thanks for the feedback.

jajatidev · Aug 14, 2023

smozgur said:
You're welcome.
I am also glad to hear that you understand the code as there are many useful functions and methods in it. Although I posted the second code to make it more efficient without using List.Accumulate, you will find the List.Accumulate function is also a very powerful function once you understand how it works.

Thanks for the feedback.

it's been a learning curve. I have been slow to revert back as I'm trying to learn before implementing.
I have in fact used the second code without the List.Accumulate for Ageing.

Thanks again. Today I have posted a new string requesting assistance with 3 excel formulas.
Hoping you would take a look at it because I came across an old post on SUMIF which I tried to replicate but couldn't make much progress.

jajatidev · Oct 30, 2023

smozgur said:
I just realized that there is a week-of-day mistake in the previous code. The Ageing step should be as follows (only the colored section is changed):

Rich (BB code):

Ageing = Table.AddColumn(ChangeTypes, "Ageing", each let startDate = Date.AddDays([ReceiveDate], 1), endDate = Date.From(DateTime.LocalNow()), Result = List.Accumulate( {0..Date.DayOfYear(endDate) - Date.DayOfYear(startDate)}, 0, (state, current) => if Date.DayOfWeek(Date.AddDays(startDate, current), Day.Monday) > 4 then state else state + 1 ) in Result ),

Also, the following modified version is not using List.Accumulate() function, and it should be much more efficient:

Power Query:

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}), " " ) ) in BlockCode

And the result:

ReceiveDate CustomerReqDate SalesOrderNo ProductNo OrderQty CompleteDelivery UserStatus Ageing AgeGroup OrderWeek CRD_Month BlockCode
6/22/2023 9/15/2023 112935380 1NR73A-K1H 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 29 [21 - 30] 25 CRDD Future INCO
7/3/2023 7/7/2023 113430548 A1A30A-KGJ 1 N BLCA C2AP EOL2 NoMP REL SCAL 22 [21 - 30] 27 CRDD Current EOL2 SCAL
7/4/2023 7/8/2023 113493710 1NR73A-K1H 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 21 [21 - 30] 27 CRDD Current INCO
7/5/2023 7/9/2023 113512547 A1A30A-KGJ 1 N C2AP EOL2 NoMP REL SCAL 20 [11 - 20] 27 CRDD Current EOL2 SCAL
7/6/2023 7/10/2023 113543813 5RA32F-KGJ 1 N C2AP MDID NoMP REL RRDC 19 [11 - 20] 27 CRDD Current RRDC
7/7/2023 7/11/2023 113561139 A1A30A-KGJ 1 N EOL2 MDID NoMP REL SCAL 18 [11 - 20] 27 CRDD Current EOL2 SCAL
7/8/2023 7/12/2023 113705782 A1A77A-KGJ 10 N C2AP EOL2 MDID NoMP REL SCAL 18 [11 - 20] 27 CRDD Current EOL2 SCAL
7/9/2023 7/13/2023 113725103 N7L37A 1 N C2AP INC-VBAP-CEPOK INCO MDID NoMP REL 18 [11 - 20] 28 CRDD Current INCO
7/10/2023 7/14/2023 113900946 2Z600F-KGJ 1 N C2AP NoMP REL SCAL 17 [11 - 20] 28 CRDD Current SCAL
7/11/2023 7/15/2023 113933893 3JA05AN-150 1 N C2AP EOL2 NoMP REL 16 [11 - 20] 28 CRDD Current EOL2
7/12/2023 7/16/2023 113969730 A1A56A-KGJ 1 N C2AP EOL2 MDID NoMP REL SCAL 15 [11 - 20] 28 CRDD Current EOL2 SCAL
7/13/2023 7/17/2023 113987750 5RA32F-KGJ 1 N COM-GLA NoMP REL 14 [11 - 20] 28 CRDD Current
7/14/2023 7/18/2023 114002150 2Z613F-KGJ 2 N C2AP NoMP REL SCAL 13 [11 - 20] 28 CRDD Current SCAL
7/15/2023 7/19/2023 114002150 5RA32F-KGJ 1 N COM-GLA NoMP REL 13 [11 - 20] 28 CRDD Current
7/16/2023 7/20/2023 114003452 2Z600F-KGJ 5 N C2AP NoMP REL SCAL 13 [11 - 20] 29 CRDD Current SCAL
7/17/2023 7/21/2023 114005180 2Z600F-KGJ 1 N C2AP NoMP REL SCAL 12 [11 - 20] 29 CRDD Current SCAL
7/18/2023 7/22/2023 114016353 A1A77A-KGJ 1 N C2AP EOL2 MDID NoMP REL SCAL 11 [11 - 20] 29 CRDD Current EOL2 SCAL
7/19/2023 7/23/2023 110113433 3GZ25A-KGJ 1 Y BLKD C2AP COM-GLA NoMP REL SCAL 10 [6 - 10] 29 CRDD Current SCAL
7/20/2023 7/24/2023 110113433 K5L35A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC 9 [6 - 10] 29 CRDD Current RRDC
7/21/2023 7/25/2023 110113433 3GZ25A-KGJ 1 Y BLKD C2AP COM-GLA NoMP REL SCAL 8 [6 - 10] 29 CRDD Current SCAL
7/22/2023 7/26/2023 110113433 K5L35A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDC 8 [6 - 10] 29 CRDD Current RRDC
7/23/2023 7/27/2023 110113433 Y3D03A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 8 [6 - 10] 30 CRDD Current
7/24/2023 7/28/2023 110113433 Z7C07A 1 Y BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 7 [6 - 10] 30 CRDD Current
7/25/2023 7/29/2023 110113433 3PZ35A-KGJ 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP 6 [6 - 10] 30 CRDD Current XBOP
7/26/2023 7/30/2023 110113433 D7P27A 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL 5 [0 - 5] 30 CRDD Current
7/27/2023 7/31/2023 110113433 3PZ35A-KGJ 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOP 4 [0 - 5] 30 CRDD Current XBOP
7/28/2023 8/1/2023 110113433 D7P27A 1 Y C2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL 3 [0 - 5] 30 CRDD Current
7/29/2023 8/2/2023 110433907 3GZ25A-KGJ 1 Y BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP 3 [0 - 5] 30 CRDD Current SCAL XBOP
7/30/2023 8/3/2023 110433907 K5L35A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 3 [0 - 5] 31 CRDD Current
7/31/2023 8/4/2023 110433907 Y3D03A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 2 [0 - 5] 31 CRDD Current
8/1/2023 8/5/2023 110433907 Z7C07A 1 Y BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL 1 [0 - 5] 31 CRDD Current
8/2/2023 8/6/2023 110433907 3GZ25A-KGJ 1 Y BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP 0 [0 - 5] 31 CRDD Current SCAL XBOP

Hi @smozgur,
I'm reassessing the UserStatus column to change the approach of capturing the BlockCode. I need to look at the UserStatus field and retain the Block Codes that appear after "REL".

smozgur · Oct 30, 2023

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.

Power Query:

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

jajatidev · Nov 30, 2023

smozgur said:

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.

Power Query:

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

Thanks, @smozgur,
Couldnt respond sooner, as I was preoccupied and just got back to focusing on the developments I have been pursuing.
I'll run the code and let you know. And I'm sure it's gonna work as expected.

Need assistance with Power Query

jajatidev

Board Regular

Excel Facts

smozgur

BatCoder

smozgur

BatCoder

jajatidev

Board Regular

jajatidev

Board Regular

smozgur

BatCoder

jajatidev

Board Regular

jajatidev

Board Regular

smozgur

BatCoder

jajatidev

Board Regular

Similar threads

Forum statistics

Share this page

Need assistance with Power Query

Board Regular

Excel Facts

BatCoder

BatCoder

Board Regular

Board Regular

BatCoder

Board Regular

Board Regular

BatCoder

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock