- the situation when the Power BI integration has been set up based on the first few submissions, then more submissions come in which have fields that hadnât been populated in the initial load - i.e. PBI doesnât already have columns for those fields. I think this is what is being referred to by @stephanealoo here
This is something that can be updated on the Power BI side but can be a bit of a faff.
The thing is, when using the UI to Expand Results then you select the list of columns to expand - but not all are automatically loaded dynamically every time. You can manually go in after there are more submissions, and manually click the âload moreâ button then select them all and save
If you look, you can see that when using the PowerBI UI to expand columns then all the column names are explicitly listed, so when new fields come through they will be ignored as theyâre not on the list. e.g.:
However it is also possible to code the PowerBI side to be more extensible - this often requires editing in the Advanced Editor etc. - i.e. by editing the Powerquery/M Language rather than clicking via the UI.
For example, using a trick from this YouTube video we managed to set it up so our Powerquery handles new fields automagically (in this case data_repeat is the column we want to expand to its fullest no matter how many underlying columns there are):
#"Dynamically Expanded data_repeat" = Table.ExpandRecordColumn(#"Replaced Value to handle nulls", "data_repeat", Record.FieldNames(Record.Combine(Table.Column(#"Replaced Value to handle nulls", "data_repeat")))),
We were using it in the context of repeating sections combining data from multiple unrelated repeats had to do a bit of extra jiggery pokery to handle nulls etc. - Iâm sure the below could be done more elegantly but here is our Powerquery code if itâs useful to anyone:
#"Expanded data_repeat" = Table.ExpandListColumn(#"Renamed Columns", "data_repeat"),
#"Replaced Value to handle nulls" = Table.ReplaceValue(#"Expanded data_repeat",null,[null_record = "null_record"],Replacer.ReplaceValue,{"data_repeat"}),
#"Dynamically Expanded data_repeat" = Table.ExpandRecordColumn(#"Replaced Value to handle nulls", "data_repeat", Record.FieldNames(Record.Combine(Table.Column(#"Replaced Value to handle nulls", "data_repeat")))),
#"Handle scenario where no null records" = if Table.HasColumns(#"Dynamically Expanded data_repeat", {"null_record"}) then #"Dynamically Expanded data_repeat" else Table.Combine({#"Dynamically Expanded data_repeat", Table.FromRows({},{"null_record"})}),
#"Filtered Rows to remove repeats with nothing selected" = Table.SelectRows(#"Handle scenario where no null records", each ([null_record] = null)),
#"Removed null helper column" = Table.RemoveColumns(#"Filtered Rows to remove repeats with nothing selected",{"null_record"}),
Lastly,
- There are also other considerations when PBI is not loading as one expects - e.g. sometimes need to hit ârefresh allâ and poss clear the cache if columns added. Also PowerBI only profiles the first 1000 rows⌠so if the new fields are appearing after the 1000 row mark then Iâm not sure how it handles. But Iâve only hit that problem once and handled it manually at that point
I believe you can manually change a setting to profile based on the entire data set but then it would take longer.
Most of my issues have been to do with 1 and 2 above. We should consider putting something in the suggestion box about #1 as I know several colleagues have hit this stumbling block also.
