Find, If its not a last record

[kulkarni21vinee]

I wanted to append delimiter | (pipe symbol) to differentiate each Maintenance Record, of a List, unless it’s not last record

Also, specific Maintaince Records would be filtering out based upon if-else conditions.

e.g.:

with below M query, when I loop through each Maintenance Description it append an unnecessary  | (pipe symbol) at the end. 

#"Extracted Values" = Table.TransformColumns(
#"Filtered Rows",
{
"WorkLoadDetails",
each Text.Combine(
List.Transform(
_,
each (
if not Text.StartsWith(Record.Field(_, "MaintenanceDescription"), "Specific Code")
then
Record.Field(_, "MaintenanceDescription")
else
""
)& (
if not Text.StartsWith(Record.Field(_, "MaintenanceDescription"), "Specific Code")
then
"|"
else
""
)
)
),
type text
}
)
in
#"Extracted Values"

My need is to remove remove extra pipe delimiter, which gets added after last record for each row (sample example shown with 3 red arrows in second screenshot).

[Ahmedx · Accepted solution]

pls try this

=Text.BeforeDelimiter([Column1],"|",RelativePosition.FromEnd)

[Ahmedx]

pls try this

[
    a= Text.Replace([WorkLoadDetails],"SC1, ",""),
b = Splitter.SplitTextByAnyDelimiter({", "})(a),
c = Text.Combine(b,"|")
][c]

[kulkarni21vinee]

Thank you @Ahmedx @ example which was shared, was just for demo. I have edited post with how data and entire query actually looks like just to give clearity

[Ahmedx]

the easy way is to add a new sun and write

=Text.Range([Column1],0,Text.Length([Column1])-1)

[kulkarni21vinee]

This is very interesting solution, however, it replaces delimiter pipe between all the WorkLoadDetails. I just need to remove extra which gets added after last record.

[Ahmedx]

it replaces only the last character

but you just need to add a new column

[kulkarni21vinee]

[Ahmedx · Accepted solution]

pls try this

=Text.BeforeDelimiter([Column1],"|",RelativePosition.FromEnd)

[kulkarni21vinee]

Doesn't work for multiple values, This strips off all values post second field record

Really appriciate you help.

[Ahmedx · Accepted solution]

pl try

=Text.Combine( List.RemoveLastN( Text.ToList([Column1]),1))

[kulkarni21vinee]

Thank you @Ahmedx ! Appreciate your help

[Omid_Motamedise]

Use the following code

let
#"Filtered Rows" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTQVQh2NtRRCMlXSMlXcIIxnJVidcAKjIAKIEIuMDk3pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkLoadDetails = _t]),
Transform = Table.TransformColumns(#"Filtered Rows",{{"WorkLoadDetails", each Text.Replace(Text.Combine(List.RemoveItems(Text.Split(_," "),{"SC1,"})),",","|")}})
in
Transform

[kulkarni21vinee]

Thank you @Omid_Motamedise  example which was shared, was just for demo. I have edited post with how data and entire query actually looks like just to give clearit

[Poojara_D12]

Hi @kulkarni21vinee 

As per my understanding, to avoid appending an extra pipe symbol | at the end, you can use Text.Combine directly with a delimiter without manually adding | . Modify your code as follows:

#"Extracted Values" = Table.TransformColumns(
    #"Filtered Rows",
    { "WorkLoadDetails", each Text.Combine(List.Transform(_, each Record.Field(_, "MaintenanceDescription")), " | ") }
)

The Text.Combine function allows you to specify a delimiter ( " | "  ), and it automatically ensures no trailing delimiter is added. No need to check for the last record manually. 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[kulkarni21vinee]

Thank you @Poojara_D12  for quick response. Rather, I tried that before, but I think I didn't include additional requirement - Sorry about that, (Updated original question). I have to filter out specific MaintenanceDescription thus have to loop in each records and filter not required one and append |. If i use | outside each record field, It appends | to filtered records. e.g. output:  | | ENG IDG OIL/FILTER