Gathering data from Folder (excel files)

[alan7lp]

Hello Experts,

I would like to ask whether the following is possible or not and what kind of solution can be  applied for it. Currently I have it working but modifying the excel files before get in them to Power BI through the querry. 

I receive a report every week where I need to unpivot a lot of columns in order to make sense of the data to create reports in PBI. The problem is that the headers of the columns I need to unpivot change from week to week (moving week):

Example:

Article Num	Article Name	Destination	Code	202001	202002	202003	202004	202005	202006	202007
1	XXX	CA	A1	2	6	8	677	878	234	234
2	WWW	DE	B2	3	1241	234	43	7	455	765
3	RRR	US	C3	5	12	3	3567	43	234	555
4	TTT	CZ	C4	7	465	324	935	234	453	234

The following week, column 202001 will disappear (or better, will be replaced by 202002) and the last column will be a new one, in this case 202008

Article Num	Article Name	Destination	Code	202002	202003	202004	202005	202006	202007	202008
1	XXX	CA	A1	2	6	8	677	878	234	234
2	WWW	DE	B2	3	1241	234	43	7	455	765
3	RRR	US	C3	5	12	3	3567	43	234	555
4	TTT	CZ	C4	7	465	324	935	234	453	234

If I tell the querry to use the headers of one or the other file, I will always get an error telling me that this or that column doesn't exist.

Is there any way that this can be done without losing the original headers from each week file?

The output should look like this: 

Start

End

In the 2 images below you can see the moving weeks.

I hope the explanation is clear enough, otherwise please let me know how can I help for your to help me 🙂

Thanks a lot,

Regards,

[Jimmy801]

Hello @alan7lp 

as already @Anonymous has mentioned you can apply the Unpivot-function of Power Query. More specifically in your case the Table.UnpivotOtherColumns function. Just select all columns not to be unpivoted and choose this function from the menu

Here the complete solution

let
	Source = #table
	(
		{"Article Num","Article Name","Destination","Code","202001","202002","202003","202004","202005","202006","202007"},
		{
			{"1","XXX","CA","A1","2","6","8","677","878","234","234"},	{"2","WWW","DE","B2","3","1241","234","43","7","455","765"},	{"3","RRR","US","C3","5","12","3","3567","43","234","555"},	
			{"4","TTT","CZ","C4","7","465","324","935","234","453","234"}
		}
	),
    UnpivotOther = Table.UnpivotOtherColumns
	(
		Source, 
		{"Article Num", "Article Name", "Destination", "Code"}, 
		"Year/Month", "Value"
	)
in
	UnpivotOther

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[alan7lp]

Hi @Jimmy801 , @Anonymous 

Thanks for your promtp replies. Unfortunately, that's not the issue (I guess it's quite complicated to explain it by writing).

I am leaving here the XL sample files. Would it be possible for you to create a new query which grabs this files (3) from the folder and see by yourself what I meant, please?

Notice that whether you select to have the headers from the first file or the last one, except for Article Num Article Name Destination Code, the rest won't match (due to this moving week that happens with each new report).

I think it will be easier to keep diving into a possible solution (if any) from there where you will visualize the issue I am facing.

This is the link where the files are uploaded. I am sorry but I couldn't find another way to share them in here: 

https://we.tl/t-lNyOo5xg1m

Thanks once again and please, let me know 🙂

Cheers.

[Jimmy801]

Hello @alan7lp 

sorry, I don't get the point. You can use the Table.UnpivotOther and define your fixed column, like destination or code. Here the complete code. And there is nowhere specified a column name of your changing columns

let
	Source = #table
	(
		{"WEEK","Article Num","Article Name","Destination","Code","202003","202004","202005","202006","202007","202008","202009"},
		{
			{"202003","1","XXX","CA","A1","2","6","8","677","878","234","234"},	{"202003","2","WWW","DE","B2","3","1241","234","43","7","455","765"},	{"202003","3","RRR","US","C3","5","12","3","3567","43","234","555"},	
			{"202003","4","TTT","CZ","C4","7","465","324","935","234","453","234"}
		}
	),
    UnpivotOhter = Table.UnpivotOtherColumns(Source, {"WEEK", "Article Num", "Article Name", "Destination", "Code"}, "year/month", "value")
in
	UnpivotOhter

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[alan7lp · Accepted solution]

Nevermind guys, I have finally found the solution after looking for it for more than 6 months!

Please, have a look on this video https://www.youtube.com/watch?v=mZbD8aduIJU

It's exactly the problem I was facing (when having new columns plus having the right data under the exact column). In this case, where no data for that column I will get "null" which is exactly what I needed and most importantly, it's dynamic.

Final result:

Thanks for your time though, I truly appreciate it 🙂 

[Jimmy801]

Hello @alan7lp 

fine you found the solution.

Problem was that I didn't get what you needed. You just spoke about unpivoting columns that are changing, not how to read them dynamically from differents sheets.

All the best

Jimmy

[alan7lp]

Thanks @Jimmy801 ,

You're totally right, it was very confusing and I didn't know how to explain it better. Need to improve in that if I want to get help in the future!

Like I said, appreciate the time invested to try to help me anyways 🙂

Have a good mate!

Cheers.

[Jimmy801]

Thanks for the Feedback

the most important thing, and also the most difficult thing is to describe really the content of what is really needed. Because when we are talking about m-language its programming and it's crucial for somebody that want to help that the goal is cleary specified. And I know how hard it can be to describe this.

have a nice day

Jimmy

[Anonymous]

I would recommend unpivoting the data everytime you import.  Power BI works better with unpivoted data anyway.  You can still build your pivoted table in the visual anyway, so there won't be any loss. 

EDIT:  Power Query has a pivot/unpivot function