Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
How can do I get the filename, split, and use it into a new column? I would like to have a column with 1 first string: Custom with content =ETL and Custom2 = excel
Solved! Go to Solution.
@twister8889 , not very clear , refer
https://techcommunity.microsoft.com/t5/excel/power-query-get-file-name/td-p/1429886
https://www.sqlservercentral.com/blogs/include-file-name-in-content-using-power-query
Hi @twister8889 ,
If you connect to the file directly, I'm afraid you cannot get the file name directly in power query.
You can try to connect to folder and try this query:
let
folder = "C:\Users\xxx\xxx\excel folder",
Source = Folder.Files(folder),
RemoveUnused = Table.SelectColumns(Source,{"Content","Name"}),
Addtables = Table.AddColumn(
RemoveUnused,
"Custom",
each Table.PromoteHeaders(Excel.Workbook([Content]){[Item = "Sheet1",Kind = "Sheet"]}[Data])
),
Addtables2 = Table.AddColumn(
Addtables,
"Custom2",
each Table.PromoteHeaders(Excel.Workbook([Content]){[Item = "Sheet2",Kind = "Sheet"]}[Data])
),
#"Removed Columns" = Table.RemoveColumns(Addtables2,{"Content"})
in
#"Removed Columns"
You will get a File name column and two table columns under this query.
Now you can split the Name column twice one by space and another by . , remove unnecessary columns and rename the new column:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
First of all, thank you for your answers and I'm sorry for the delay.
When the scenario is to get one file and sheets with the same structure, these options to solve the problem. I'm new in power bi and in my project, so I noticed that I need to get data of multiples files (excel) with different sheets structures. However, I will be open another thread to this scenario, for this I will mark the first answer as correct, but all solved my problem.
Thank you so much, guys.
@twister8889 - Had another thought on this. Make the file name/path a query parameter. Then you can set it and use it to create a new column in your query.
@twister8889 , not very clear , refer
https://techcommunity.microsoft.com/t5/excel/power-query-get-file-name/td-p/1429886
https://www.sqlservercentral.com/blogs/include-file-name-in-content-using-power-query
@twister8889 - Maybe check out a Folder query
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |