Forum Discussion
Find, If its not a last record
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).
pls try this
=Text.BeforeDelimiter([Column1],"|",RelativePosition.FromEnd)pl try
=Text.Combine( List.RemoveLastN( Text.ToList([Column1]),1))
14 Replies
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=04iw9SYI2HN80HKSThank 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
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
TransformThank 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
pls try this
[ a= Text.Replace([WorkLoadDetails],"SC1, ",""), b = Splitter.SplitTextByAnyDelimiter({", "})(a), c = Text.Combine(b,"|") ][c]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
the easy way is to add a new sun and write
=Text.Range([Column1],0,Text.Length([Column1])-1)
