Automatic data transformation

[ms166]

Is it possible to program something in Power BI so every time a new data sheet is added to the report, the data can be transformed automatically and someone wouldn't have to do it manually every time?

[az38 · Accepted solution]

hi @ms166 

if data format is the same on each excel sheet you can easy combine them in Power Query like

et
    Source = Excel.Workbook(File.Contents("C:\file.xlsx"), null, true),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"})
in
    #"Expanded Data"

[selimovd]

Hey @ms166 ,

can you provide more details?

When new data is appearing it will be transformed automatically. So what exactly is the question?

Best regards

Denis

[ms166]

As in, when a new data sheet is inputted, is it possible to automate the removal of rows and columns. This is needed because the Excel version of the data looks different to how it is needed in Power BI.