Tranpose/Pivot of multiple excel files

[Molin]

Dear Community, 

I am struggeling with a dataset from a supplier which comes in daily in .xlsx format in a sharepoint folder. So far there is no problem in connecting and getting the data into Power Query, however the format is nowhere near perfect for PBI reporting purposes. I would like to transform all the files into a simple table with the name of the file kept as a column as it contains date + other values to be used. The remaining values should be pivotted meaning the Business Units should be on the rows and the current rows should be on seperate columns. 

I hope someone is able to help me out. 

Thanks in advan 

[v-jingzhang]

Hi @Molin 

You can use SharePoint Folder connector to query all files from a sharepoint folder and combine them into one single query. In the combined query, you will have file names in a column. 

Then use @rohit_singh 's solution to transform the query "Transform Sample File". Transformations performed in this sample query will be applied to all files combined in the combined query.

If you see an error in the combined query after transforming the sample file (this is usually caused by the automatically generated "Changed Type" step in combined query as it refers to old column names before transformation), you can remove the "Changed Type" step in the combined query. This could solve the error most of the time. Change column types manually after all data is combined correctly.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[rohit_singh · Accepted solution]

Hi @Molin ,

Thanks for sending across the details. Here is what you can try in Power Query :

1) Load the excel file

2) Add a column that has all transformations performed. I have briefly explained these below :

a) Expand the data column 

b) Remove columns

c) Remove null rows

d) Transpose the table

e) Promote headers

f) Filter out "Total" from column 1 and rename columns

 3) Remove other columns

4) Expand column "Custom"

You will see that the field "Name" is now available with your dataset to identify the datasource.

Here is the code : 

let
    Source = Excel.Workbook(File.Contents("Sample Data.xlsx"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.SelectRows(Table.PromoteHeaders(Table.Transpose(Table.SelectRows(Table.RemoveColumns(Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),{"Name","Item", "Kind", "Hidden"}), each [Column2] <> null))), each [Column1] <> "Total")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data", "Item", "Kind", "Hidden"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Total Hours", "Toal Wage Cost", "Total Head Count", "Total Shifts"}, {"Column1", "Total Hours", "Toal Wage Cost", "Total Head Count", "Total Shifts"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Column1", "BusinessUnit"}, {"Toal Wage Cost", "Total Wage Cost"}})
in
    #"Renamed Columns"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!  😊

[AlexisOlson]

Try Demoting headers, Transposing the table, and the Promoting headers.

Promote and Demote buttons are here:

(Transpose is under the Transform tab.)

[rohit_singh]

HI @Molin ,

Please provide sample data in text format (not a screenshot) and the expected output.

Kind regards,

Rohit

[Molin]

Hi Rohit, 

Thanks for reaching out. Here is a sample dataset: https://we.tl/t-YJWX4Uu9DH

The expected output woud ideally look something like this: 

Thanks a lot! 

Kind Regards 

Aleksander