Forum Discussion

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
2 years ago
Solved

Count n'th delimiter value

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

Need Help
  • ThxAlot's avatar
    ThxAlot
    2 years ago
    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's avatar
    ThxAlot
    2 years ago

    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's avatar
    ThxAlot
    2 years ago
    #"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")

9 Replies

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    2 years ago
    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's avatar
      StuartSmith
      Icon for Power Participant rankPower Participant
      2 years ago

      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's avatar
        ThxAlot
        Icon for Super User rankSuper User
        2 years ago

        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"