Forum Discussion
Get Data from folder
I am tring to get data from folder where i have multiple excel files but when i am tring combine and transform after Expanded table
query i am getting data with column headers which are static i only what data without header this is happing only in one column
how to get data without static headers
let
Source = Folder.Files("\\server-new\D\SERVER DATA BACKUP\Server Data Backup\Consultancy\Power BI\Clients Analysis\Dashboard\ERP\Sales"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"
this are the steps in power query
i want to combine multile
My first 2 lines may be different in different excel so i want to remove those 2 lines before headers get promoted but when i expand table automatically headers are getting promoted ,i wanted to promate headers after removing 2 lines but not able to do so becasue when i expand data it automatically getting headers prommoted for one column
can anyone tel me how to get cololumns after expand table wither romoting headers so i can remover 2 rows then use pramote header step
Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi, Shivkanyabyale
Thanks for reaching out to the Microsoft fabric community forum.The most straightforward solution at present is to add a column to delete rows based on the differences in Source.Name:
1.Here is my sample data:
2.I inserted a custom column:
if [Source.Name]= "1.csv" then Table.RemoveFirstN([Transform File], 3) else if [Source.Name]= "2.csv" then Table.RemoveFirstN([Transform File], 5) else [Transform File]In the red box above, you can adjust the M language as needed:Table.RemoveFirstN(), Table.RemoveLastN(), and Table.RemoveRows() functions.
For more details, please refer to:
Table.RemoveFirstN - PowerQuery M | Microsoft Learn
Table.RemoveLastN - PowerQuery M | Microsoft Learn
Table.RemoveRows - PowerQuery M | Microsoft Learn3.Then proceed to delete the excess columns.
4.Below is the complete M language:
let Source = Folder.Files("C:\Users\v-linyulu\Desktop\combine"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each if [Source.Name]= "1.csv" then Table.RemoveFirstN([Transform File], 3) else if [Source.Name]= "2.csv" then Table.RemoveFirstN([Transform File], 5) else [Transform File]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Transform File"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Country", "City", "Latitude", "Longitude", "Sales"}, {"Custom.Country", "Custom.City", "Custom.Latitude", "Custom.Longitude", "Custom.Sales"}) in #"Expanded Custom"Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Modify the "Transform File" function or implement the combine yourself if you want to avoid all the extra boilerplate code.
Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi, Shivkanyabyale
Thanks for reaching out to the Microsoft fabric community forum.The most straightforward solution at present is to add a column to delete rows based on the differences in Source.Name:
1.Here is my sample data:
2.I inserted a custom column:
if [Source.Name]= "1.csv" then Table.RemoveFirstN([Transform File], 3) else if [Source.Name]= "2.csv" then Table.RemoveFirstN([Transform File], 5) else [Transform File]In the red box above, you can adjust the M language as needed:Table.RemoveFirstN(), Table.RemoveLastN(), and Table.RemoveRows() functions.
For more details, please refer to:
Table.RemoveFirstN - PowerQuery M | Microsoft Learn
Table.RemoveLastN - PowerQuery M | Microsoft Learn
Table.RemoveRows - PowerQuery M | Microsoft Learn3.Then proceed to delete the excess columns.
4.Below is the complete M language:
let Source = Folder.Files("C:\Users\v-linyulu\Desktop\combine"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each if [Source.Name]= "1.csv" then Table.RemoveFirstN([Transform File], 3) else if [Source.Name]= "2.csv" then Table.RemoveFirstN([Transform File], 5) else [Transform File]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Transform File"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Country", "City", "Latitude", "Longitude", "Sales"}, {"Custom.Country", "Custom.City", "Custom.Latitude", "Custom.Longitude", "Custom.Sales"}) in #"Expanded Custom"Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your responce Anonymous ,your solution solved my problem.
