How to change the data type of all columns of my Power BI source table starting from a specific colu-CodePudding

I have a power BI file which is being feeded by an Excel file.

When the table is loaded into the power BI one of the steps is to change the data type from 'text' to 'number' of all columns except the first two columns. The table of my Excel file may change over time having more or less columns but I want to apply this transformation for all the columns (except the first two) regardless of the size of the table.

How can I do this?

Thanks in advance!

CodePudding user response：

This was tested in Power BI Desktop using List.Transform to transform the list of column names into the arguments for the Table.TransformColumnTypes function.

If the code you use to load your Excel data table into Power Query is something like lines two and three, then the code shown seems to work.

let
    Source = Excel.Workbook(File.Contents("C:\Users\ron\Desktop\Book1.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],

colNames = Table.ColumnNames(Table1_Table),
#"First Two" = List.Transform(List.FirstN(colNames,2), each {_, type text}),
#"Number Cols" = List.Transform(List.RemoveFirstN(colNames,2), each {_, type number}),

    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,#"First Two" & #"Number Cols")
in
    #"Changed Type"

You do have to "refresh" the query and, if you open PQ (Transform Data), you will need to update the Preview.

Ron

CodePudding user response：

Stepping back, your Power BI Dataset won't automatically pick up new columns in your data source. It only looks like that because when you hit "Close and Apply" in the Power Query window, Power BI examines the Power Query output and changes the Dataset data model to match. That only happens in Power BI Desktop, not on refresh in the Power BI Service.

So dealing with new columns in the data source is always a manual process. If you need to pick up new source columns automatically the only way is to pivot those columns to rows in Power Query.