CODEWITHSUNDEEP
powerbipowerquery
Stuquan
Stuquan

Reputation: 57

Dynamically changing columns in Power BI

I have an API data source I am refreshing daily to gather power bi activity. Each day, the data returns a different amount of columns, so it might have 60 one day and 80 (+20) additional another day.

When I try to refresh the dataset in the Power BI Service, it naturally fails and states that the new columns cannot be found in the row set.

I have explored many options such as creating a combine table, however I do not know all the names of the columns that could come in each day so this failed because it was very static. Does anyone know of a way to dynamically handle these daily changes?

Many thanks

Upvotes: 0

Views: 298

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89371

The only way to refresh a data source that has changing schema is to unpivot that table and bring it into your model as key/value pairs.

enter image description here

Upvotes: 1

JSmart523
JSmart523

Reputation: 2497

It depends on what you want to do.

If you're just trying to change all extra columns from type any to type number, you can try something like

let
  Source = #"table with extra columns",
  OtherColumnNames = List.RemoveItems(
    Table.ColumnNames(Source),
    #"List of known column names"
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    List.Transform(
      OtherColumnNames,
      each {_, type number}
    )
  )
in
  #"Changed Type"

or, if that's something you will be doing to multiple tables you could turn it into a function, like a query with a name of "fTransformOtherColumnTypes" with the following code.

(
  #"List of known column names" as list,
  #"table with extra columns" as table,
  Type as type
) =>
let
  Source = #"table with extra columns",
  OtherColumnNames = List.RemoveItems(
    Table.ColumnNames(Source),
    #"List of known column names"
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    List.Transform(
      OtherColumnNames,
      each {_, Type}
    )
  )
in
  #"Changed Type"

and then your other queries can use it, e.g. fTransformOtherColumnTypes({"name","color","org", "alias"}, #"your source data", type number)

Upvotes: 0

Related Questions