Forum Discussion
Splitter.SplitTextByAnyDelimiter when some delimiters and their values are missing
Super UserHi MFR
You can add several Custom Columns and use Text.BetweenDelimiters
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlExVTA2NTCzNDUxMFRQUUkC8oBUooKLq66RgaGRrgGIm6WQrhSrg1N1qoKbS7CCl66vQgBYr1eAv6efXoiBqaUJTHMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Local Param" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Local Param", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "$5", each if Text.Contains([Local Param], "$$5") then Text.BetweenDelimiters( [Local Param] , "$$5 ", " $$") else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "$e", each if Text.Contains([Local Param], "$$e") then Text.BetweenDelimiters( [Local Param] , "$$e ", " $$") else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "$aCustom", each if Text.Contains([Local Param], "$$a") then Text.BetweenDelimiters( [Local Param] , "$$a ", " $$") else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "$j", each if Text.Contains([Local Param], "$$j") then Text.BetweenDelimiters( [Local Param] , "$$j ", " $$") else null)
in
#"Added Custom3"
Regards
Phil
Hi Phil,
Thank you very much for your proposed solution. I managed to get what I wanted !
My final query in Excel is as follows :
let
Source = Excel.CurrentWorkbook(){[Name="Tableau6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Local Param", type text}}),
#"Split Local Param by row" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Local Param", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Local Param"),
#"Custom$5" = Table.AddColumn(#"Split Local Param by row", "$5", each if Text.Contains([Local Param], "$$5") then Text.BetweenDelimiters( [Local Param] , "$$5 ", " $$") else ""),
#"Custom$b" = Table.AddColumn(#"Custom$5", "$b", each if Text.Contains([Local Param], "$$b") then Text.BetweenDelimiters( [Local Param] , "$$b ", " $$") else ""),
#"Custom$e" = Table.AddColumn(#"Custom$b", "$e", each if Text.Contains([Local Param], "$$e") then Text.BetweenDelimiters( [Local Param] , "$$e ", " $$") else ""),
#"Custom$a" = Table.AddColumn(#"Custom$e", "$a", each if Text.Contains([Local Param], "$$a") then Text.BetweenDelimiters( [Local Param] , "$$a ", " $$") else ""),
#"Custom$j" = Table.AddColumn(#"Custom$a", "$j", each if Text.Contains([Local Param], "$$j") then Text.BetweenDelimiters( [Local Param] , "$$j ", " $$") else "")
in
#"Custom$j"
Regards,
- PhilipTreacy
Hi MFR
Glad to hear it.
If my answer solved your problem please mark it as the solution so others can learn from this.
Regards
Phil