Custom Column with variable - Power BI

Question

A variable create pointing a table value - var1. Variable refers to a column name.

I want to use this variable to create a custom column. Up to now a use the below formulas:

• "Text.Middle(" & var1 & ",3,Text.Length(" & var1 & ")-3)"

With this formula i get the formula as string.

• Text.Middle(var1,3,Text.Length(var1")-3)

With this formula i get the part of the column name instead of the part of each row.

Any ideas?

Answer 1

Apologies, I finally understand your question. You want to use a variable as a column name. In that case, you will need to use Record.Field(_, var1) to loop through each row.

I assume you have a table called "Table1" in Excel:

Column Name
EUR1500
USD1000
CHF200
GBp40

Below code works for me:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
  var1 = "Column Name",
  #"Added custom" = Table.AddColumn(Source, "Custom", each Text.Middle(Record.Field(_, var1),3,Text.Length(Record.Field(_, var1))-3))
in
  #"Added custom"