Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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).
Solved! Go to Solution.
pls try this
=Text.BeforeDelimiter([Column1],"|",RelativePosition.FromEnd)
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)
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.
it replaces only the last character
but you just need to add a new column
pls try this
=Text.BeforeDelimiter([Column1],"|",RelativePosition.FromEnd)
Doesn't work for multiple values, This strips off all values post second field record
Really appriciate you help.
pl try
=Text.Combine( List.RemoveLastN( Text.ToList([Column1]),1))
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
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
53 | |
27 | |
15 | |
14 | |
13 |