Expand number of columns after each merged workbook?

BatTodor

New member
Joined
Nov 6, 2016
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2012 (Build 13530.20376)
Hi all,
Working to prepare database generated from similar information I failed to expand number of columns after each merged workbook.
I build PowerQuery, based on solution read in excelguru's bolg (/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/)

In original workbooks I have 9 columns and variable rows (more than 80). The structure of each workbook is like this:
Store | Address of store | Data for Monday | Data for Tuesday | Data for Wednesday | Data for Thursday | Data for Friday | Data for Saturday | Data for Sunday |
I want to remove second column and transpose information, because to have dates on first column and list of stores on other columns.
The number of stores varies.
I was able to merge all workbooks in one database, but still not able to expand it depends of number of stores. Every new store can be in any position. For example:

Workbook 1
Store | Address of store | Date01 | Date02 | Date03 | Date04 | Date05 | Date06 | Date07 |
Store 1 | Address of store 1 | 12 | 14 | 16 | 18 | 20 | 22 | 24 |
Store 2 | Address of store 2 | 13 | 15 | 17 | 19 | 21 | 23 | 25 |
Store 3 | Address of store 3 | 22 | 24 | 26 | 28 | 30 | 32 | 34 |
Store 4 | Address of store 4 | 23 | 25 | 27 | 29 | 31 | 33 | 35 |
Workbook 2
Store | Address of store | Date08 | Date09 | Date10 | Date11 | Date12 | Date13 | Date14 |
Store 1 | Address of store 1 | 32 | 34 | 36 | 38 | 40 | 42 | 44 |
Store 2 | Address of store 2 | 33 | 35 | 37 | 39 | 41 | 43 | 45 |
New Store1 | Address of New store1 | 22 | 24 | 26 | 28 | 30 | 32 | 34 |
Store 3 | Address of store 3 | 42 | 44 | 46 | 48 | 50 | 52 | 54 |
Store 4 | Address of store 4 | 43 | 45 | 47 | 49 | 51 | 53 | 55 |


Workbook 3
Store | Address of store | Date15 | Date16 | Date17 | Date18 | Date19 | Date20 | Date21 |
Store 1 | Address of store 1 | 42 | 44 | 46 | 48 | 50 | 52 | 54 |
Store 2 | Address of store 2 | 43 | 45 | 47 | 49 | 51 | 53 | 55 |
New Store1 | Address of New store1 | 32 | 34 | 36 | 38 | 40 | 42 | 44 |
Store 3 | Address of store 3 | 52 | 54 | 56 | 58 | 60 | 62 | 64 |
New Store2 | Address of New store1 | 72 | 74 | 76 | 78 | 80 | 82 | 84 |
Store 4 | Address of store 4 | 53 | 55 | 57 | 59 | 61 | 63 | 65 |


Merged database need to look like this:
Date | Store 1 | Store 2 | NS1 | Store 3 | NS2 | Store 4 |
Date01 | 12 | 13 | | 22 | | 23 |
Date02 | 14 | 15 | | 24 | | 25 |
Date03 | 16 | 17 | | 26 | | 27 |
Date04 | 18 | 19 | | 28 | | 29 |
Date05 | 20 | 21 | | 30 | | 31 |
Date06 | 22 | 23 | | 32 | | 33 |
Date07 | 24 | 25 | | 34 | | 35 |
Date08 | 32 | 33 | 22 | 42 | | 43 |
Date09 | 34 | 35 | 24 | 44 | | 45 |
Date10 | 36 | 37 | 26 | 46 | | 47 |
Date11 | 38 | 39 | 28 | 48 | | 49 |
Date12 | 40 | 41 | 30 | 50 | | 51 |
Date13 | 42 | 43 | 32 | 52 | | 53 |
Date14 | 44 | 45 | 35 | 54 | | 55 |
Date15 | 42 | 43 | 32 | 52 | 72 | 53 |
Date16 | 44 | 45 | 34 | 54 | 74 | 55 |
Date17 | 46 | 47 | 36 | 56 | 76 | 56 |
Date18 | 48 | 49 | 38 | 58 | 78 | 59 |
Date19 | 50 | 51 | 40 | 60 | 80 | 61 |
Date20 | 52 | 53 | 42 | 62 | 82 | 63 |
Date21 | 54 | 55 | 44 | 64 | 84 | 65 |

Can anybody help me?
 
Can I suggest that your Merged db look like this?

DateStoreAmount
10/11/201611
10/11/201622
10/11/201633
10/11/2016 NS14
10/11/2016 NS25
11/11/201616
11/11/201627
11/11/201638
Then you can Pivot as per your example.

Can you post the M you have so far?
 
Hello Comfy,
My final goal is to pivot information from all files. As I realized need to automate in this steps:
1. Merge all information in one table. I have information for each week and will be During merging want to transpose from this structure:
StoreAddress07.11.201608.11.201609.11.201610.11.201611.11.201612.11.201613.11.2016
111121314151617
221222324252627
331323334353637
441424344454647

to this:
Date1234
07.11.201611213141
08.11.201612223242
09.11.201613233343
10.11.201614243444
11.11.201615253545
12.11.201616263646
13.11.201617273747

Everything looks OK till in some week new store appear:
StoreAddress14.11.201615.11.201616.11.201617.11.201618.11.201619.11.201620.11.2016
111121314151617
221222324252627
New191929394959697
331323334353637
441424344454647
After this I'm not able to do this:
Date12New134
07.11.20161121913141
08.11.20161222923242
09.11.20161323933343
10.11.20161424943444
11.11.20161525953545
12.11.20161626963646
13.11.20161727973747
2. After merging all workbooks for all dates and store and get all data in this structure:
Date12New13New24New3...Last
07.11.201611219131814171551
...
last day99999999899979559

3. During merging want to add helper columns like this:
DateInsertedDayofWeekISOWeekNumberHelpForPivot12New13New24New3...Last
07.11.2016Monday201645CW1645:07.11.2016:Monday11219131814171551
...201645
31.12.2016201652CW1652:31.12.2016:Saturday99999999899979559
4. I saw video in youtube - watch?v=-mzpLg1gI98 and with this HelpForPivot column will be able to produce table for pivoting. Will think how to automate this step to be possible to update info when power query add information. Finally structure for information need to be as you suggest. A little bit more rows because of additional information - calendar week and day of the week, but will be possible to handle and analyze it. Will be great to transform data at once with query, but not sure that will be possible.

I'm newbie in using Power Query, but for now code looks like this

I made 2 queries - one for transposing data as function:

Query 1
let
fnModifyAllFiles = (#"Directory containing Excel files to combine" as text) =>
let
Source = Excel.Workbook(File.Contents((#"Directory containing Excel files to combine" as text)), null, true),
#"Sheet1" = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Sheet1",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",8),
#"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column2", "Column3", "Column11"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns"),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Promoted Headers",1),
#"Transposed Table" = Table.Transpose(#"Removed Bottom Rows"),
#"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table"),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"Column1", "Store"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Store", type date}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Store"})
in
#"Removed Errors"
in
fnModifyAllFiles

In fact failed to make variation depends of source workbook. Some files are .xls, some are .xlsx, but I have also .xlsb. As I noticed there is different way for reading data depends of file (natural excel or binary):
if workbook is .xls or .xlsx do this:
#"Sheet1" = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
else if workbook is .xlsb do this:
#"Sheet1" = Source{[Item="Sheet1"]}[Data],

Another query is for merging all files in folder:
Query 2
let
Source = Folder.Files("<FolderWithData>"),
#"Filtered Rows1" = Table.SelectRows(Source, each not Text.StartsWith([Name], "~")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Name", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "MyExcelData", each fnModifyAllFiles([Folder Path]&[Name])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"MyExcelData"}),
#"Expanded MyExcelData" = Table.ExpandTableColumn(#"Removed Other Columns", "MyExcelData", {"1", "2", "3", "4"}, {"1", "2", "3", "4", "Store"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded MyExcelData",{"Store", "1", "2", "3", "4"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Store", "Date"}})
in
#"Renamed Columns"

I know that need a little magic in step #"Expanded MyExcelData" and #"Reordered Columns", but not able to get columns from each file and expand it.

Will be great if will be possible to implement way of preparing pivot-ready information, as it described in youtube - watch?v=-mzpLg1gI98 in last query.

Can you help me?
 
That video is very old, Power Query has a function to UnPivot data.

I've attached 3 sample files (place them in a folder)

and an example file (you will need to update the folder Path in the Main query)

Hopefully this will be enough for you to update your Query.

I think it best I post these as I can't replicate your samples exactly (it appears as though you have more than the 9 columns you posted in your sample as well as data before and after your tables)
 

Attachments

  • Book1.xlsx
    9.6 KB · Views: 5
  • Book2.xls
    27 KB · Views: 7
  • Book3.xlsb
    8.6 KB · Views: 7
  • 6959 Example.xlsx
    24 KB · Views: 1
Last edited:
Wrong example file uploaded.

Use this one.

Also, something to note. You don't have to use an if statement for the different types of Excel files, SheetData = Source{[Name="Sheet1"]}[Data] should work for all.
 

Attachments

  • Book3.xlsb
    8.6 KB · Views: 12
  • Book2.xls
    27 KB · Views: 8
  • Book1.xlsx
    9.6 KB · Views: 9
  • 6959 Example.xlsx
    24.1 KB · Views: 8
Last edited:
Hi Comfy,
You are genius! Thanks a lot for lesson! :)

Best regards
 
Back
Top