CODEWITHSUNDEEP
excelpowerquery
Gab_de_Fort
Gab_de_Fort

Reputation: 3

Excel Power Query Merge and Transform columns

I am trying to combine 2 columns into one and then replace "Null" rows with the value in an adjacent columns using Excel Power Query. SO far I haven't been able to resolve this issue. These are my unsuccessful attempts:

Attempt 1:

= Table.ExpandTableColumn(Source, "F 52 AGR_1016", {"AGR_NAME"}, {"F 52 AGR_1016.AGR_NAME"}),
 else Table.ReplaceValue(Source, each "", Replacer.ReplaceValue{"New value of line"})

Attempt 2:

= Table.ExpandTableColumn(Source, "F 52 AGR_1016", {"AGR_NAME"}, {"F 52 AGR_1016.AGR_NAME"}), 
if #"F 52 AGR_1016" = "" then Replacer.ReplaceValue("","",{"New Value of line"}), else

I get the following error message, however Excel does not show me where exactly that error is:

Expression.SyntaxError: Token Eof expected.

Upvotes: 0

Views: 415

Answers (1)

horseyride
horseyride

Reputation: 21393

It is a bit hard to tell what you are doing, and the code format is incorrect. You can't append if then to Table.ExpandTableColumn

To merge two column: click select them, right click, then choose merge columns

To add a column that tests other column values, add column .. custom column ... and use = if xxx then yyy else zzz

= if [Col1] = null then [Col2] else [Col3]

This code expands three columns; merges the text value of Column1 and Column2 into a new column called Merged; creates Column4 where the result is Merge if Column3 is a null, and Column3 if Column3 is not a null

...
#"Expanded" = Table.ExpandTableColumn(Source, "AllRows", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
#"Merged Columns" = Table.CombineColumns(#"Expanded",{"Column2", "Column1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom4", each if [Column3]=null then [Merged] else [Column3])

Upvotes: 1

Related Questions