Forum Discussion

Amica's avatar
Amica
Frequent Visitor
6 years ago
Solved

Help with a custom column

Hi,

 

I need some help with a custom column in Power Query please. I am trying to extract data from a column that is in a data dictionary. 

 

The following code works for the first row of my data, where I have inputted the id "9a1644e7-4750-5170-ad73-1026aa728836":

 

 

  #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each [model.PendingMandateConfigurations] [#"9a1644e7-4750-5170-ad73-1026aa728836"])
in
  #"Added Custom"

 

 

However, I need it to work for each row, not the first row only. The id that is referenced above is also in the column "model.LatestConfigurationId". I am struggling to include the column name in the code. Everything I have tried returns an error.  

 

Please let me know if there is a solution for this. 

 

Thanks so much

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Amica ,

     

    Would you please try to modify your query :

     

      #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each [model.PendingMandateConfigurations] [PayingTenantId])
in
  #"Added Custom"

     

6 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    6 years ago

    Hi Amica,

     

    Would you please show us some sample data and your expected output? Or you can use the extract function in power query editor without writing M-query:

    Best Regards,

    Dedmon Dai

    • Amica's avatar
      Amica
      Frequent Visitor
      6 years ago

      Thanks for the reply v-deddai1-msft and camargos88 

      The text behind this record:

      Is this snippet:

       

      "PendingMandateConfigurations":{"9a1644e7-4750-5170-ad73-1026aa728836":{"PayingTenantId":"c9071157-7837-4011-a509-9621a117b397","TenantAccoundId":"08373538-f290-08d7-e956-4a3253e310cb","RentalContribution":{"Amount":12000.0,"IsoCode":"ZAR"},"PaymentOption":0,"StartDate":"2020-06-01","EndDate":"2021-05-31"}}

       

      We used a custom function to pull out the record associated to a given key (9a1644e7-4750-5170-ad73-1026aa728836). Which works for the first row when we hard code the ID. But understandably, it doesn't work for the other rows because it is hard coded. How can we make this function reference the column of LatestConfigurationId so that the data pulls dynamically for each row and not the hard coded value?

      The following is what is behind the record in the custom column:

      Thanks so much