Forum Discussion

K Kumar's avatar
K Kumar
Copper Contributor
Oct 22, 2017
Solved

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

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,

excel
power bi
Power Query
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 22, 2017

    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

     

4 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 22, 2017

    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

     

    • K Kumar's avatar
      K Kumar
      Copper Contributor
      Oct 23, 2017

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Oct 23, 2017

        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

         

Resources