Get Data from folder

[Shivkanyabyale]

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 

[v-linyulu-msft · Accepted solution]

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 Learn

3.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.

[Shivkanyabyale]

Thank you for your responce @v-linyulu-msft ,your solution solved my problem.

[lbendlin]

Modify the "Transform File"  function  or implement the combine yourself if you want to avoid all the extra boilerplate code.