SOLVED
Home

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

Highlighted
K Kumar
New 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
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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies