Using variables in custom column with power query

[Jeffery24]

Hi,

Am new to power query and struggling to find an answer to my issue of a variable lookup column.

In my dataset I have lot's of freight shipment data containing date fields, there are subsequent DAX formulas using these date fields to drive the dashboard statistics. These formulas can change each time dependant on customer setup.

An example formula is 

= “Setup - Arrival EventA” – “Setup - Arrival EventB”

On upload of the data source I then add the custom columns in the editor layer.

= Table.AddColumn(#"Changed Type1", " Setup - Arrival EventA ", each [Vessel Arrived])

= Table.AddColumn(#"Changed Type1", " Setup - Arrival EventB ", each [Estimated Time of Arrival])

At the moment I have to change the base refence fields all manually.

Vessel Arrived and Estimated Time of Arrival are both fields from data source and contain dates for the calculationsI would like and have tried to make the custom column dynamic by referencing the value from the parameter as a variable so the custom column returns the chosen date column from the dataset to be used further in the dashboard.

= Table.AddColumn(#"Changed Type1", " Setup - Arrival EventA ", each [“ &([Arrival EventA])& ”)

The Setup - Arrival EventA column would then return the arrival Vessel Arrived field.

Is there a method using a variable in a custom column?

Any advice would be appreciated, thanks.

[ImkeF · Accepted solution]

Not exactly sure about your requirement, but could it be this?:

Table.AddColumn(#"Changed Type1", " Setup - Arrival EventA ", each Record.Field(_, #"Arrival EventA")

[Jeffery24]

Hi Imke,

You saved my week! It was just missing a bracket off end 

Table.AddColumn(#"Changed Type1", " Setup - Arrival EventA ", each Record.Field(_, #"Arrival EventA"))

Thanks so much, really appreciate it.

[Jeffery24]

Hi Imke,

Thanks for quick response. Apologies if I haven't explained the issue.

I get the following error "Token Comma expected"  when using the code. Is there something I've missed. Thanks

   #"Expanded Container Type" = Table.ExpandTableColumn(#"Merged Queries", "Container Type", {"Referance", "Max Fill", "Max Weight", "TEU", "Target Fill"}, {"Referance", "Max Fill", "Max Weight", "TEU", "Target Fill"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Container Type",{{"Referance", "Container Type"}, {"TEU", "TEU Set"}, {"Target Fill", "Cont Analysis Target Fill"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Cont Analysis Target Fill", Percentage.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", " Setup - Arrival EventA ", each Record.Field(_, #"Arrival EventA")
in
    #"Added Custom"