Forum Discussion
Solved
Automatic data transformation
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?
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"
3 Replies
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"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.
