Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |