Oct 22 2017 10:43 AM
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,
Oct 22 2017 11:08 AM
SolutionHi,
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
Oct 23 2017 02:54 AM
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
Oct 23 2017 03:11 AM
You may ignore privacy level to avoid such kind of errors (there are pros and cons, however)
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
Oct 23 2017 05:46 AM
Great,
Thanks Sergei!
Oct 22 2017 11:08 AM
SolutionHi,
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