cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cthardwik Frequent Visitor
Frequent Visitor

Automatic Data Transformation

I have lots of different spreadsheets that are all the same format, but with different data in them. At the moment I have to upload each speadsheet individualy, carry out the transformations of the data in query editor and then appended them all together. Is there a way this can be done automatically?

 

For example is there a way with using Get Data from Folders?  Can Power BI select all the specific tables in the spreadsheets and then carry out a pre deturmined set of transformations on the data? And then perhaps even append them all together?

 

(All the spreadsheets have a lot of tables in that I am not interested in, but I only upload the same 3 tables from each spreadsheet, which are all in the same format)

 

The transformations I carry out on each table are:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content]
,
   #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Date", "Status"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Date", type date}})
in
    #"Changed Type"

 

So basically, transposed table, promote first row as headers, unpivot a set amount of columns, change type of one of the rows to date.

 

Thanks

1 REPLY 1
Steve_Wheeler Established Member
Established Member

Re: Automatic Data Transformation

If you have, or can create, a table of the workbooks you want (with the same Table in each) then you can process them all with your transformations set up as a function - e.g. see https://www.powerpivotpro.com/2015/07/consolidated-worksheets-with-power-query/

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)