Forum Discussion

jscivias's avatar
jscivias
Helper I
2 years ago

Issues with automatically applied steps when accessing updated data from API

Hello, I currently have a dataflow housed in power bi premium workspace, with data being sourced through an API call that adds an extra month worth of data every month. The problem is that when I source the data, Power Query automatically applies steps e.g. "expanded column" of existing monthly data (say, January & February), which means that when i refresh the data in March, it will still only expand Jan & Feb columns and ignore the new data that now exists. For now i am just manually repairing this step to recognize the additional new columns, but is there a better solution to getting around this issue? I hope i made myself clear thanks in advance for your help.

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jscivias ,

    Try getting the column names in the record dynamically like this and then expanding it by this.

    let
    Source = {
    [Jan = 1, Name = "Bob", Phone = "123-4567"],
    [Jan = 2, Name = "Jim", Phone = "987-6543"],
    [Feb = 3, Name = "Paul", Phone = "543-7890"]
},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Value"}, null, ExtraValues.Error),
    ExpandList= List.Distinct(List.Combine(List.Transform(Table.Column( #"Converted to Table", "Value"), each if _ is record then Record.FieldNames(_) else {}))),
    Expand= Table.ExpandRecordColumn( #"Converted to Table", "Value", ExpandList,ExpandList)
in 
    Expand

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

    • jscivias's avatar
      jscivias
      Helper I
      2 years ago

      Hi there, thanks for your suggestion - I tested out your code in advanced editor and saw that it worked well, then I only changed the source to my own (API call) but for some reason the column to be expanded doesn't expand. Here is mine, the only difference being my source:

       

      but the last step expand results in this:

       

       

      where the "value" column gets new data added every month (e.g. next month when i refresh data it will have Apri 2024):

       

       

      as the final step doesn't expand the columns dynamically, if i manually expand as my next step, i am left with the same issue.. thanks again for any suggestions