SOLVED

August 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Copper Contributor

Hi,

 

I am attempting to create a new column where each element is a number, but that number is a value that I have recorded in a named cell/range in my workbook.

 

I have a cell named SCALAR that has a value 1.18 and have tried the below but couldn't get it to work (I've even tried to change the column type).

 

#"Added Custom14" = Table.AddColumn(#"Added Custom10", "Custom.1", each Excel.Workbook("SCALAR"){0}[Column1])

 

Thanks,

4 Replies
best response confirmed by K Kumar (Copper Contributor)
Solution

Hi,

 

If SCALAR is the cell name in the same workbook when you shall use Excel.CurrentWorkbook

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddColumn = Table.AddColumn(Source, "Custom", each Excel.CurrentWorkbook(){[Name="SCALAR"]}[Content]{0}[Column1])
in
    AddColumn

See attached

 

Thanks for the reply Sergei,

 

I am trying to add the column to an existing table , I've made some other adjustments the last one being #"Added Custom10" (the last column I added) and have tried to add the below line of code but get an error. The error seems to be after the "each" statement so if I replace Excel.Current... with "1" it will print a column of 1's.

 

#"Added Custom14" = Table.AddColumn(#"Added Custom10", "Custom.1", each Excel.CurrentWorkbook(){[Name="SCALAR"]}[Content]{0}[Column1])

 Formula.Firewall: Query 'MyNewTable' (step 'Added Custom14') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

Thanks again

You may ignore privacy level to avoid such kind of errors (there are pros and cons, however)

PrivacyLevel.JPG

but in this case better (from performance point of view as well) pickup the constant separately and after that add it to the column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceScalar = Excel.CurrentWorkbook(){[Name="SCALAR"]}[Content]{0}[Column1],
    AddColumn = Table.AddColumn(Source, "Custom", each SourceScalar)
in
    AddColumn

 

1 best response

Accepted Solutions
best response confirmed by K Kumar (Copper Contributor)
Solution

Hi,

 

If SCALAR is the cell name in the same workbook when you shall use Excel.CurrentWorkbook

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddColumn = Table.AddColumn(Source, "Custom", each Excel.CurrentWorkbook(){[Name="SCALAR"]}[Content]{0}[Column1])
in
    AddColumn

See attached

 

View solution in original post