Issues with automatically applied steps when accessing updated data from API

[jscivias]

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.

[Anonymous]

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]

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