Hi,
I am getting data from Sharepoint where I have excel files in different folders.
I need to add a column to my date that shows the name of the folder where the file source is.
Example:
Folder "sharepoint/folder 1/:
Folder "sharepoint/folder 2/:
What I need is, on the combined table that I get on Power BI, add a column, for each line, with the reference of folder and file of origin:
Can you help me?
Thanks
Alvaro
Solved! Go to Solution.
I assume you are opening the files via the Combine operation and your query steps look something like this, where the items in the red square were created by Power Query automatically by the Combine operation:
Go to the "Removed Other Columns1" step, and click the little gear icon. Check the "Folder Path" box and hit OK.
Go to the end of your query steps and you'll have a new folder path column where those files are located. You can then use Text.Start, Text.Middle, Text.End, or whatever to create whatever text you want to combine with the folders.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI assume you are opening the files via the Combine operation and your query steps look something like this, where the items in the red square were created by Power Query automatically by the Combine operation:
Go to the "Removed Other Columns1" step, and click the little gear icon. Check the "Folder Path" box and hit OK.
Go to the end of your query steps and you'll have a new folder path column where those files are located. You can then use Text.Start, Text.Middle, Text.End, or whatever to create whatever text you want to combine with the folders.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGlad to help @AkshayManke
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThat's it!
That is wath I need and I did not know
Thanks a lot
Alvaro
Great. That folder path column is available when you start, but the Combine operation automatically hides it and most other columns once it opens the actual files. I go back in and add them back for similar reasons all of the time. 👍
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Alvarom1 as @edhans you go to step where you connect to sharepoint folder and make sure you can see folder path column in there and then check if you removed this column in any of the following step, if yes, don't remove this column so that is is part of your table , similar for filename and after that you can use any Text functions to concatenate File Name and Folder Path
Hope this help. If something is not clear, provide more details with example what is missing or what need to be done.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks!
Alvaro
@Alvarom1 when you connect to sharepoint folder it has a column called Folder Path and you can use that to concatenate with file name
Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
HI thanks,
When I combined all the excel files with power bi I get a long list with all the lines from those excel. On that one, I do not have the path folder. What I need is , on that combined table, be able to add part of the text of the folder with each line(file) is coming from.
Makes sense?
Thanks
Alvaro
User | Count |
---|---|
106 | |
88 | |
69 | |
52 | |
49 |
User | Count |
---|---|
148 | |
94 | |
79 | |
71 | |
70 |