Forum Discussion
Count n'th delimiter value
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"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"#"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")
You omit "?" operator in Text.StartsWith(_, dd)){0}?
It evaluates to null if the index is out of range.
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
#"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")ThxAlot for all your help 🙂