Solved: Automatic data transformation

ms166 · ‎10-10-2024

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 · ‎10-10-2024

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"

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

az38 · ‎10-10-2024

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"

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

selimovd · ‎10-10-2024

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 · ‎10-10-2024

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.