Count n'th delimiter value

[StuartSmith]

Is there a way to count the value of the x delimiter in a column?

I have a table similar to the below...

The staffing column is created by an MS Flow and is in the format of DD-StaffingValue; DD-StaffingValue; ... throughtout the month.

Country		Building		Staffing		Date		Test
UK		LON01		01-0;02-0;03-2;04-2;05-2;06-2;07-2;08-2;09-2;10-2;11-2;12-2;13-2;14-2;15-2;16-2;17-2;18-2;19-2;20-2;21-2;22-2;23-2;24-2;25-2;26-2;27-2;28-2;29-2;30-2;31-2;		01 October 2023		2
UK		LON01		01-3;02-3;03-16;04-16;05-20;06-20;07-20;08-20;09-20;10-20;11-20;12-20;13-20;14-20;15-20;16-20;17-20;18-20;19-20;20-20;21-20;22-20;23-20;24-20;25-20;26-20;27-20;28-20;29-20;30-20;31-20;		01 November 2023		20
UK		LON01		01-0;02-0;03-8;04-8;05-8;06-8;07-8;08-8;09-8;10-8;11-8;12-8;13-8;14-8;15-8;16-8;17-8;18-8;19-8;20-8;21-8;22-8;23-8;24-8;25-8;26-8;27-8;28-8;29-8;30-8;31-8;		01 Decemeber 2023		8
UK		LON01		01-0;02-0;03-3;04-3;05-3;06-3;07-3;08-3;09-3;10-3;11-3;12-3;13-3;14-3;15-3;16-3;17-3;18-3;19-3;20-3;21-3;22-3;23-3;24-3;25-3;26-3;27-3;28-3;29-3;30-3;31-3;		01 January 2024		3

I want to try and create a calculated column that will check todays date "DD" and display the staffing value for the corrosponding DD value.  

I've got as far as... 🤣

Test =

VAR TodaysDate = FORMAT(TODAY(),"DD")

VAR Staffing = 

Return

TodaysDate

Thanks in advance

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxTgNhEEPhu2ydSJ4xBKMcAQQVVZT7X4M8NxQg0cxW/vW9vd2Or7fjdLx/fmgeX81ZVy3H573qifPMuXBeOOG8Ps6IM5zlsBgWw2JYDIthMSyWxbJYFstiWSyLZbEslsWyMAuzOO6n31iDNdi5oOU+nlK9KlgVq2TVrKJVtcpW3Spclat01a7iVb3KV/1qgFqgJqgNaoRaoWaoHWqI/i75+e0hJHSEjFARIkJDSAgFISD4Az/oAz7YAz3IAzy4AzuoAzqYAzmIAzh4Azdo8x/WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WD+z9Gw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Building = _t, Staffing = _t]),
    #"Extracted Staffing" = Table.AddColumn(Source, "Nr", each let dd = DateTime.ToText(DateTime.LocalNow(), "dd") in Text.RemoveRange(List.Select(Text.Split([Staffing],";"), each Text.StartsWith(_, dd)){0}, 0, 3))
in
    #"Extracted Staffing"

[ThxAlot · Accepted solution]

Simple enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxTsNQFETRvbhOpPlvIAzKEoigQrIUZf/bwHcoKECiea7GPtf3+/b5tp2228e71vHUOuuq4fg8Vz1xnjkXzgsnnNfjLHEWZzgsFovFYrFYLBaLxWJYDIthMSyGxbAYFsNiWAwLszCL7XH6jTVYg10XtNzjVapXBatilayaVbSqVtmqW4WrcpWu2lW8qlf5ql8NUAvUBLVBjVAr1Ay1Qw3R3yU/vz2EhI6QESpCRGgICaEgBAR/4Ad9wAd7oAd5gAd3YAd1QAdzIAdxAAdv4AZt/sMarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMH6G7vvB3LvPb6u7fH4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Building = _t, Staffing = _t]),
    #"Extracted Staffing" = Table.AddColumn(Source, "Nr", each let dd = DateTime.ToText(DateTime.LocalNow(), "dd") in Text.RemoveRange(List.Select(Text.Split([Staffing],";"), each Text.StartsWith(_, dd)){0}?, 0, 3) ?? "n/a")
in
    #"Extracted Staffing"

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxTgNhEEPhu2ydSJ4xBKMcAQQVVZT7X4M8NxQg0cxW/vW9vd2Or7fjdLx/fmgeX81ZVy3H573qifPMuXBeOOG8Ps6IM5zlsBgWw2JYDIthMSyWxbJYFstiWSyLZbEslsWyMAuzOO6n31iDNdi5oOU+nlK9KlgVq2TVrKJVtcpW3Spclat01a7iVb3KV/1qgFqgJqgNaoRaoWaoHWqI/i75+e0hJHSEjFARIkJDSAgFISD4Az/oAz7YAz3IAzy4AzuoAzqYAzmIAzh4Azdo8x/WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WYA3WD+z9Gw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Building = _t, Staffing = _t]),
    #"Extracted Staffing" = Table.AddColumn(Source, "Nr", each let dd = DateTime.ToText(DateTime.LocalNow(), "dd") in Text.RemoveRange(List.Select(Text.Split([Staffing],";"), each Text.StartsWith(_, dd)){0}, 0, 3))
in
    #"Extracted Staffing"

[StuartSmith]

Thanks, I got that working, although how can I handle errors, as there could be an example where there may not be a matching date in the "Staffing" column, such as a new site or a site closure.  

 iF this happens, could I put a "0" or "N/A" or something like that?

[ThxAlot · Accepted solution]

Simple enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxTsNQFETRvbhOpPlvIAzKEoigQrIUZf/bwHcoKECiea7GPtf3+/b5tp2228e71vHUOuuq4fg8Vz1xnjkXzgsnnNfjLHEWZzgsFovFYrFYLBaLxWJYDIthMSyGxbAYFsNiWAwLszCL7XH6jTVYg10XtNzjVapXBatilayaVbSqVtmqW4WrcpWu2lW8qlf5ql8NUAvUBLVBjVAr1Ay1Qw3R3yU/vz2EhI6QESpCRGgICaEgBAR/4Ad9wAd7oAd5gAd3YAd1QAdzIAdxAAdv4AZt/sMarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMEarMH6G7vvB3LvPb6u7fH4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Building = _t, Staffing = _t]),
    #"Extracted Staffing" = Table.AddColumn(Source, "Nr", each let dd = DateTime.ToText(DateTime.LocalNow(), "dd") in Text.RemoveRange(List.Select(Text.Split([Staffing],";"), each Text.StartsWith(_, dd)){0}?, 0, 3) ?? "n/a")
in
    #"Extracted Staffing"

[StuartSmith]

Thanks, I tried that, but still get the errors...

So as an example, ID 1453, within "Power Query Editor", the column value for "NR" is "Error"...

Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]

because there is no data for todays date.

Whereas ID 1453 outside of "Power Query Editor", the column value for "NR" is empty, which is acceptable, but doesn't display the "N/A".

dd = DateTime.ToText(DateTime.LocalNow(), "dd") in Text.RemoveRange(List.Select(Text.Split([Region Attrition],";"), each Text.StartsWith(_, dd)){0}, 0, 3)?? "N/A"),

And therefore, when I try to refresh the tables, the refresh is cancelled , because of the errors.

How can I fix this? Thanks in advacne.

[StuartSmith]

OK, I just created a new step to replace the errors with "0" and this seems to have fixed the issue, unless you know of an additional issues this method may cause?

[ThxAlot]

You omit "?" operator in Text.StartsWith(_, dd)){0}?

It evaluates to null if the index is out of range.

[StuartSmith]

I still can't figure out the correct code to take into account if a date isnt present.  The current code is...

#"Added Custom1" = Table.AddColumn(#"Changed Type2", "nr", each let dd = DateTime.ToText(DateTime.LocalNow(), "dd") in Text.RemoveRange(List.Select(Text.Split([Region Attrition],";"), each Text.StartsWith(_, dd)){0}, 0, 3)?? "N/A"),

and produces the error...

Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]

I have tried...

each Text.StartsWith(_, dd)){0}, 0, 3) "N/A"),
each Text.StartsWith(_, dd)){0}, 0, 3) N/A),

each Text.StartsWith(_, dd)){0}, 0, 3)),

But still get the above error.

RESOLVED: Additionally, currently your code get todays date number and then pulls the corrosponding value from the staffing column. Is it possible to also have a column that is today -1, so the previous day from today.  Hope that makes sence. RESOLVED

[ThxAlot · Accepted solution]

#"Added Custom1" = Table.AddColumn(#"Changed Type2", "nr", each let dd = DateTime.ToText(DateTime.LocalNow(), "dd") in Text.RemoveRange(List.Select(Text.Split([Region Attrition],";"), each Text.StartsWith(_, dd)){0}?, 0, 3)?? "N/A")

[StuartSmith]

ThxAlot for all your help 🙂