cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tamir Member
Member

Import from folder where files have tables with different names

Hi,

 

I get a monthly excel file which is generated by another program and contains a table with a running number in the name.

 

Meaning, each file, contains a table with a different name (same structure).

 

When I want to import the folder into Power BI I get an error and I think it is due to the different names of the tables.

 

Please see the file and the folder zipped here

 

Thank you 

Tamir

1 ACCEPTED SOLUTION

Accepted Solutions
Rfranca Member
Member

Re: Import from folder where files have tables with different names

HI @Tamir

 

in the query editor
copy and paste this code
do not forget to change the location of the folder where the files are.

 

 

let
    Fonte = Folder.Files("C:\Users\Desktop\Import folder"),
    #"Personalização Adicionada" = Table.AddColumn(Fonte, "Personalizar", each Excel.Workbook([Content])),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada",{"Personalizar", "Name"}),
    #"Personalizar Expandido" = Table.ExpandTableColumn(#"Outras Colunas Removidas", "Personalizar", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Personalizar Expandido", each ([Kind] = "Table")),
    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Linhas Filtradas",{"Name.1", "Data", "Name"}),
    #"Data Expandido" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "Data", {"Date", "Sales"}, {"Date", "Sales"}),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Data Expandido",{"Name", "Name.1", "Date", "Sales"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Reordenadas",{{"Name", type text}, {"Name.1", type text}, {"Date", type date}, {"Sales", type number}})
in
    #"Tipo Alterado"
4 REPLIES 4
Rfranca Member
Member

Re: Import from folder where files have tables with different names

HI @Tamir

 

in the query editor
copy and paste this code
do not forget to change the location of the folder where the files are.

 

 

let
    Fonte = Folder.Files("C:\Users\Desktop\Import folder"),
    #"Personalização Adicionada" = Table.AddColumn(Fonte, "Personalizar", each Excel.Workbook([Content])),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada",{"Personalizar", "Name"}),
    #"Personalizar Expandido" = Table.ExpandTableColumn(#"Outras Colunas Removidas", "Personalizar", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Personalizar Expandido", each ([Kind] = "Table")),
    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Linhas Filtradas",{"Name.1", "Data", "Name"}),
    #"Data Expandido" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "Data", {"Date", "Sales"}, {"Date", "Sales"}),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Data Expandido",{"Name", "Name.1", "Date", "Sales"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Reordenadas",{{"Name", type text}, {"Name.1", type text}, {"Date", type date}, {"Sales", type number}})
in
    #"Tipo Alterado"
Tamir Member
Member

Re: Import from folder where files have tables with different names

Hi @Rfranca

 

The code works great.

 

Thank you kindly,

Tamir

Tamir Member
Member

Re: Import from folder where files have tables with different names

For anyone who wishes to use the code in its English version:

let
    Source = Folder.Files("C:\Users\user\OneDrive - Tamir Basin\Power BI\Import folder"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "CustomTamir", each Excel.Workbook([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoke Custom Function1",{"CustomTamir", "Name"}),
    #"Expanded CustomTamir" = Table.ExpandTableColumn(#"Removed Other Columns", "CustomTamir", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded CustomTamir", each ([Kind] = "Table")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name.1", "Data", "Name"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Date", "Sales"}, {"Date", "Sales"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Name.1", type text}, {"Date", type date}, {"Sales", Int64.Type}, {"Name", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Name", "FileName"}, {"Name.1", "TableName"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "FileName", "TableName", "Sales"})
in
    #"Reordered Columns"

 

Rfranca Member
Member

Re: Import from folder where files have tables with different names

hi @Tamir

 

 

the commands of the M functions are always in English so you can use them without problems, only the names of the query steps can be changed.

example:
# "Expanded Data" =
# "Expanded data" =

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 3,026 guests
Please welcome our newest community members: