Forum Discussion
August 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in
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 AddColumnSee attached
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
AddColumnSee 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)
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 AddColumnGreat,
Thanks Sergei!
