Forum Discussion

Jeffery24's avatar
Jeffery24
Helper I
7 years ago
Solved

Using variables in custom column with power query

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.

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

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

     

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

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

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

     

    Table.AddColumn(#"Changed Type1", " Setup - Arrival EventA ", each Record.Field(_, #"Arrival EventA")
    • Jeffery24's avatar
      Jeffery24
      Helper I
      7 years ago

      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"

       

       

    • Jeffery24's avatar
      Jeffery24
      Helper I
      7 years ago

      Hi Imke,

       

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

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

      Thanks so much, really appreciate it.