Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Gathering data from Folder (excel files)

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 NumArticle NameDestinationCode202001202002202003202004202005202006202007
1XXXCAA1268677878234234
2WWWDEB231241234437455765
3RRRUSC35123356743234555
4TTTCZC47465324935234453234

 

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 NumArticle NameDestinationCode202002202003202004202005202006202007202008
1XXXCAA1268677878234234
2WWWDEB231241234437455765
3RRRUSC35123356743234555
4TTTCZC47465324935234453234

 

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,

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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 🙂 

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Anonymous 

         

        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