Expand number of columns after each merged workbook?

[BatTodor]

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?

 

[Comfy]

Can I suggest that your Merged db look like this?

Date	Store	Amount
10/11/2016	1	1
10/11/2016	2	2
10/11/2016	3	3
10/11/2016	NS1	4
10/11/2016	NS2	5
11/11/2016	1	6
11/11/2016	2	7
11/11/2016	3	8

Then you can Pivot as per your example.

Can you post the M you have so far?

 

[BatTodor]

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:

Store	Address	07.11.2016	08.11.2016	09.11.2016	10.11.2016	11.11.2016	12.11.2016	13.11.2016
1		11	12	13	14	15	16	17
2		21	22	23	24	25	26	27
3		31	32	33	34	35	36	37
4		41	42	43	44	45	46	47

to this:

Date	1	2	3	4
07.11.2016	11	21	31	41
08.11.2016	12	22	32	42
09.11.2016	13	23	33	43
10.11.2016	14	24	34	44
11.11.2016	15	25	35	45
12.11.2016	16	26	36	46
13.11.2016	17	27	37	47

Everything looks OK till in some week new store appear:

Store	Address	14.11.2016	15.11.2016	16.11.2016	17.11.2016	18.11.2016	19.11.2016	20.11.2016
1		11	12	13	14	15	16	17
2		21	22	23	24	25	26	27
New1		91	92	93	94	95	96	97
3		31	32	33	34	35	36	37
4		41	42	43	44	45	46	47

After this I'm not able to do this:

Date	1	2	New1	3	4
07.11.2016	11	21	91	31	41
08.11.2016	12	22	92	32	42
09.11.2016	13	23	93	33	43
10.11.2016	14	24	94	34	44
11.11.2016	15	25	95	35	45
12.11.2016	16	26	96	36	46
13.11.2016	17	27	97	37	47

2. After merging all workbooks for all dates and store and get all data in this structure:

Date	1	2	New1	3	New2	4	New3	...	Last
07.11.2016	11	21	91	31	81	41	71		551
...
last day	99	99	99	99	89	99	79		559

3. During merging want to add helper columns like this:

Date	InsertedDayofWeek	ISOWeekNumber	HelpForPivot	1	2	New1	3	New2	4	New3	...	Last
07.11.2016	Monday	201645	CW1645:07.11.2016:Monday	11	21	91	31	81	41	71		551
...		201645
31.12.2016		201652	CW1652:31.12.2016:Saturday	99	99	99	99	89	99	79		559

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?

 

[Comfy]

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)

 

[Comfy]

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.

 

[BatTodor]

Hi Comfy,
You are genius! Thanks a lot for lesson!

Best regards