Forum Discussion
Power ParticipantCount 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")
Power ParticipantOK, 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?
Super UserYou 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
#"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 🙂