Hi All,

i'm new to the M Query language and looking for some help please. I'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the error 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

I plan to change the expression to test for multiple conditions however I need to get the basic expression working first :-)

The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?

Thank you for your time

John

let
FullFilePath = "C:\PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Data.Column1", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9"}),
    #"Filled Down" = Table.FillDown(#"Expanded Data",{"Data.Column4"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Filled Down", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filtered Rows" = Table.SelectRows(#"Removed Blank Rows", each ([Name] = "1998-99" or [Name] = "1999-00" or [Name] = "2000-01" or [Name] = "2001-02" or [Name] = "2002-03" or [Name] = "2003-04" or [Name] = "2004-05" or [Name] = "2005-06" or [Name] = "2006-07" or [Name] = "2007-08" or [Name] = "2008-09" or [Name] = "2009-10" or [Name] = "2010-11" or [Name] = "2011-12" or [Name] = "2012-13" or [Name] = "2013-14" or [Name] = "2014-15")),
    #"Replace Values" = if [Data.Column4] = "London" then Table.ReplaceValue(#"Filtered Rows", "null", "London", Text.Replace, {[Data.Column5]}) else Table.ReplaceValue(#"Filtered Rows", "null", "null", Text.Replace, {[Data.Column5]})
in
    #"Replace Values"

• Edited by Shiangoli Thursday, July 16, 2015 2:31 PM

I'm thinking that you are going to have to use something like a Record.TransformFields operation instead or a Table.TransformColumns operation

https://support.office.com/en-us/article/Record-TransformFields-Applies-to-Power-Query--c1bee5ce-4aeb-49b7-9bf7-efb7c63772c2?ui=en-US&rs=en-US&ad=US

https://support.office.com/en-us/article/Table-TransformColumns-Applies-to-Power-Query--523173e7-96ed-4d33-918b-4329b4be6223?ui=en-US&rs=en-US&ad=US

Hi Seth

Thank you for your reply

I tried the following and got the same error. It would appear i'm doing something fundamentally wrong somewhere

let
FullFilePath = "C:\PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Data.Column1", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9"}),
    #"Filled Down" = Table.FillDown(#"Expanded Data",{"Data.Column4"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Filled Down", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filtered Rows" = Table.SelectRows(#"Removed Blank Rows", each ([Name] = "1998-99" or [Name] = "1999-00" or [Name] = "2000-01" or [Name] = "2001-02" or [Name] = "2002-03" or [Name] = "2003-04" or [Name] = "2004-05" or [Name] = "2005-06" or [Name] = "2006-07" or [Name] = "2007-08" or [Name] = "2008-09" or [Name] = "2009-10" or [Name] = "2010-11" or [Name] = "2011-12" or [Name] = "2012-13" or [Name] = "2013-14" or [Name] = "2014-15")),
    #"Replace Values" = Table.TransformColumns(#"Filtered Rows", {"Data.Column5", if [Data.Column4] = "London" then "London" else [Data.Column5]})
in
    #"Replace Values"

It should be something like:

Table.TransformColumns(  
Table.SelectRows(#"Filtered Rows", each [Column5] = "London")
{{"Column5", Number.FromText},    
{"Column4", "London"}})

Hi Seth

The Table.SelectRows filters the table to only include those where column5 = "London", the final result of this is to only returns rows that have London, which is not what I need to achieve. My objective is to update a column value where the value is "London" in a different column. Is this possible?

Regards

John

I envisioned that you could use Table.Combine to combine all of the Table.TransformColumns together since you indicated that you needed to do this for multiple locations.

Hi,

Couldnt find a replace-operation that works here. This is a workaround:

   #"Replace Values" = Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(#"Filtered Rows", "Data.Column5New", each if [Data.Column4] = "London" then if [Data.Column5]=null then "London" else [Data.Column5] else [Data.Column5]),{"Data.Column5"}),{{"Data.Column5New", "Data.Column5"}})

Hi Imke,

That works perfectly,

reading it...

1.Create a new column called Data.Column5New and populate it to be "London" if Data.Column4 equals "London" and Data.Column5 equals null. I note the nested 'if' no use of 'and' 

2. Remove Data.Column5

3. Then rename Data.Column5New to Data.Column5

the trick being to create a new column then populating with 'each if'

Thank you

Regards

John