Forum Discussion

ms166's avatar
ms166
Regular Visitor
1 year ago
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...
Need Help
  • az38's avatar
    az38
    1 year ago

    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"
az38's avatar
az38
Community Champion
1 year ago

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"