Forum Discussion
If statement doesn't recognize column in Power Query
I'm trying to extract the text from a column using the following 2 scenarios:
If column contains "/" then extract the remaining word after "/".
If column contains "()" extract the word from the last parantheses.
If I test each condition separately, it works as expected but I'm unable to join these 2 conditions into a single statement with a if/else/then statement.
What am I missing?
#"Transform Values" = Table.TransformColumns(#"Duplicated Column", {{"ExtractedTranform", if each Text.Contains([Column1],"/") then each Text.AfterDelimiter(_, "/") else each Text.BetweenDelimiters(_, "(", ")", {0, RelativePosition.FromEnd}, 0), type text}} )Expression.Error: We cannot apply field access to the type Text.
Hi, I am able to achieve using Split Column and some M query.
As you can see, Col1 was initial column. Col2 and Custom are intermediate columns and 4th one is the final column.
Source:Below is the M Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjNyclXCMlILUpVitUB8fXRBUB8DbCAplJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.BetweenDelimiters([Column1.1],"(",")")),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Formatted", each if [Column1.2] <> null then [Column1.2] else if [Custom] <> "" then [Custom] else [Column1.1])
in
#"Added Conditional Column"If this helps to resolve your problem, then please mark it as solution, thanks
Samrat
1 Reply
Hi, I am able to achieve using Split Column and some M query.
As you can see, Col1 was initial column. Col2 and Custom are intermediate columns and 4th one is the final column.
Source:Below is the M Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjNyclXCMlILUpVitUB8fXRBUB8DbCAplJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.BetweenDelimiters([Column1.1],"(",")")),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Formatted", each if [Column1.2] <> null then [Column1.2] else if [Custom] <> "" then [Custom] else [Column1.1])
in
#"Added Conditional Column"If this helps to resolve your problem, then please mark it as solution, thanks
Samrat