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...
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")
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"

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

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

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

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

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

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

        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