Dynamically changing columns in Power BI

Question

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

David Browne - Microsoft · Accepted Answer · 2022-10-28 15:24:55Z

1

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.

answered Oct 28, 2022 at 15:24

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JSmart523 · Accepted Answer · 2022-10-28 12:41:12Z

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)

Collectives™ on Stack Overflow

Dynamically changing columns in Power BI

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related