Help with power query

[winds]

Hello. So recently I had a bunch of spreadsheets in separate files, and I grouped them all together back by coding the individual files using numbers (1,2,3 and etc.), copying one set of data by having one spreadsheet opened (say spreadsheet 1 into the 1st column of my master spreadsheet), then dragging that data in the master to match the number of files I wanted (about 30+), then manually find and replace each column so that I change the spreadsheet in the formula (eg. 1 to 2 in 2nd column, 1 to 3 in 3rd column). Now this was about 30+ files so it was not too bad to do manually. But in the future I may have to do the same task about a 100+ files and that would be a bit overkill to be honest. I've been told that power query should help. How do I go about it as I have never used power query so I am completely unsure how it works or where to start...

 

[winds]

Unfo

Thanks for the examples. They didn't really help me understand the data any better but I might have managed to come up with a little something to get you started.

I started by creating a dummy data file with a couple of sheets of data. Then I used Power Query to create a basic query:

let
    Source = Excel.Workbook(File.Contents("C:\TMP\DummyData.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Expanded Data1" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Filled Down" = Table.FillDown(#"Expanded Data1",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Name", "Sheet Name"}, {"Column1", "Sheet Title"}, {"Column2", "Number Column"}, {"Column3", "Name Column"}, {"Column4", "9"}, {"Column5", "10"}, {"Column6", "11"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Name Column] <> null))
in
    #"Filtered Rows1"

The query simply gets all the data from all the worksheets in the file and fills down the Sheet Title from Column B
I duplicated the query I made and turned it into a simple function that I named fnFileData:

(MyFileName as text)=>
let
    Source = Excel.Workbook(File.Contents(MyFileName), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Expanded Data1" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Filled Down" = Table.FillDown(#"Expanded Data1",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Name", "Sheet Name"}, {"Column1", "Sheet Title"}, {"Column2", "Number Column"}, {"Column3", "Name Column"}, {"Column4", "9"}, {"Column5", "10"}, {"Column6", "11"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Name Column] <> null))
in
    #"Filtered Rows1"

If you compare the codes you can see the only difference is at the very beginning: I simply changed the hard coded file name into a parameter called MyFileName.

Now that I had my function ready I can use it the folder query:

let
    Source = Folder.Files("C:\TMP"),
    #"Added Custom" = Table.AddColumn(Source, "Data", each fnFileData([Folder Path] & [Name])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Sheet Name", "Sheet Title", "Number Column", "Name Column", "9", "10", "11"}, {"Sheet Name", "Sheet Title", "Number Column", "Name Column", "9", "10", "11"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Data", {"Sheet Name", "Sheet Title", "Number Column", "Name Column"}, "Column Header", "Value")
in
    #"Unpivoted Other Columns"

The folder query appends all the data from all the worksheets and unpivots the columns "9", "10" and "11". I don't know if those are fixed names or what but I handlend them as if they were fixed. I don't even promote the headers but simply rename them just to skip a couple of rows.

The whole example query expects all the sheets in your workbooks to have the same data structure: If your data has different number of columns of data on each sheet you might want to use a more dynamic approach. That can be done with Power Query as well but that's a lot more work.

The folder query is not finished. You might want to pivot the data using the "Sheet Title" column to create the new columns. I didn't do it because I didn't know the meaning of the rest of the columns: The end result will be a lot cleaner if you get rid of all the extra columns before pivoting.

I'm so baffled how come your advanced editor has more things going on whereas mine is so simple... I must be missing a step somewhere..

Right now I've been doing Get Data -> From File -> From Folder -> Transform Data.. is that wrong??

 

[Misca]

I started with the Get Data -> From File -> From Excel Workbook because I wanted to be able to work on a single file first. Then I turned that into a function to be able to use it on every file in the folder.

So start with a single file and try to avoid having to refer to column names: Get rid of all the Detect Data Type - and Reorder Columns-steps because Power Query is so picky with names.

Unpivot Other Columns is your best friend when you're having different column headers on different sheets.

 

[winds]

I started with the Get Data -> From File -> From Excel Workbook because I wanted to be able to work on a single file first. Then I turned that into a function to be able to use it on every file in the folder.

So start with a single file and try to avoid having to refer to column names: Get rid of all the Detect Data Type - and Reorder Columns-steps because Power Query is so picky with names.

Unpivot Other Columns is your best friend when you're having different column headers on different sheets.

So very sorry but what and how exactly do you mean by turning into a function? Also since one workbook has many sheets do I want to load each sheet or transform the sheet?

Also where is the Detect Data Type under?

 

[winds]

bump?

 

[Misca]

I've tried to explain how to turn queries into custom functions several times already in my previous posts. It's not difficult at all once you get the heck of it. Check out this YouTube tutorial.

Other than that I don't know what to add. The data example you shared wasn't usefull at all since all the useful parts were blackened & I couldn't figure out what was going on.

 

[winds]

Ok I will Private you a link, in that link there are two files. Basically I need to do a tally in Column D-F, I-U, and also at the bottom box, R-U... but like across. So example, I need to tally for English, Row 13, then with the other workbook, English, Row 13 as well. Same as for the bottom box R-U, like tally the bottom box R-U for English, then the other workbook as well and so on..

 

[winds]

I forgot to mention, I don't think I have a problem doing the tallying and playing around with formulas, it's just the power query part to combine files that I think I need help with