Batch Import excel files with different column name

[k_mathana]

Hi

I have trouble to perform batch import from multiple excel files where some file column names differs for example in some file table column mentioned as "Week ID" and in some files "week" and in some files "WEEK" in some some files just "ID" and they are not in the same column some files it is in "col1" in some files it is in "col5' etc. How to manage? this I need you guidelines on this Thanks in advance

[Jimmy801 · Accepted solution]

Hello @k_mathana 

you can rename them one by one and use the "try otherwise" statement to handle the errors. Your new code could look something like this

    RenamedWeekID = try Table.RenameColumns(PreviousStep,{{"WeekID", "Week"}}) otherwise PreviousStep,
    RenameID = try Table.RenameColumns(RenamedWeekID,{{"ID", "Week"}}) otherwise RenamedWeekID

here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekID = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"WeekID", type text}}),
    RenamedWeekID = try Table.RenameColumns(PreviousStep,{{"WeekID", "Week"}}) otherwise PreviousStep,
    RenameID = try Table.RenameColumns(RenamedWeekID,{{"ID", "Week"}}) otherwise RenamedWeekID
in
    RenameID

a more elegant solution would be to maintain a translation table (see my step TransformTable) and filter the old value column by your column names of the table to be translated and apply the result in a Table.SelectRows. Here the code

    TransformTable =  #table({"old", "new"},{{"WeekID", "Week"}, {"ID", "Week"}}),
    FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(PreviousStep), _[old]))),
    RenameWeek = try Table.RenameColumns(PreviousStep, FilterForWeek) otherwise PreviousStep

here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekID = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"WeekID", type text}}),
    TransformTable =  #table({"old", "new"},{{"WeekID", "Week"}, {"ID", "Week"}}),
    FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(PreviousStep), _[old]))),
    RenameWeek = try Table.RenameColumns(PreviousStep, FilterForWeek) otherwise PreviousStep
in
    RenameWeek

 Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[k_mathana · Accepted solution]

Dear Jimmy

Thanks for the response, Yes that is the solution I am looking for. my hurdle is I am not an expert so I am finding hard when I try to connect to my folders and not able to modify your Intial source connecting lines, Could you please help?

[Jimmy801 · Accepted solution]

Hello @k_mathana 

my code you can apply to the tables read from a Excel-sheet and this what you asked for, thinking of that you have this already as a base. Depending if your Excel-files are all the same (one sheet called sheet1 etc.) you have to read first all Excel-files from your folder and then apply your query/function. As this request has nothing to do with your initial request I would propose to  mark my proposed solution as a solution to close this threat, you start out to understand your other requests you could have and open a new topic. Otherwise this here will be a never ending thread 🙂

Hope this is ok for you

Jimmy

[Jimmy801 · Accepted solution]

Hello @k_mathana 

you can rename them one by one and use the "try otherwise" statement to handle the errors. Your new code could look something like this

    RenamedWeekID = try Table.RenameColumns(PreviousStep,{{"WeekID", "Week"}}) otherwise PreviousStep,
    RenameID = try Table.RenameColumns(RenamedWeekID,{{"ID", "Week"}}) otherwise RenamedWeekID

here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekID = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"WeekID", type text}}),
    RenamedWeekID = try Table.RenameColumns(PreviousStep,{{"WeekID", "Week"}}) otherwise PreviousStep,
    RenameID = try Table.RenameColumns(RenamedWeekID,{{"ID", "Week"}}) otherwise RenamedWeekID
in
    RenameID

a more elegant solution would be to maintain a translation table (see my step TransformTable) and filter the old value column by your column names of the table to be translated and apply the result in a Table.SelectRows. Here the code

    TransformTable =  #table({"old", "new"},{{"WeekID", "Week"}, {"ID", "Week"}}),
    FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(PreviousStep), _[old]))),
    RenameWeek = try Table.RenameColumns(PreviousStep, FilterForWeek) otherwise PreviousStep

here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekID = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"WeekID", type text}}),
    TransformTable =  #table({"old", "new"},{{"WeekID", "Week"}, {"ID", "Week"}}),
    FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(PreviousStep), _[old]))),
    RenameWeek = try Table.RenameColumns(PreviousStep, FilterForWeek) otherwise PreviousStep
in
    RenameWeek

 Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[k_mathana · Accepted solution]

Dear Jimmy

Thanks for the response, Yes that is the solution I am looking for. my hurdle is I am not an expert so I am finding hard when I try to connect to my folders and not able to modify your Intial source connecting lines, Could you please help?

[Jimmy801 · Accepted solution]

Hello @k_mathana 

my code you can apply to the tables read from a Excel-sheet and this what you asked for, thinking of that you have this already as a base. Depending if your Excel-files are all the same (one sheet called sheet1 etc.) you have to read first all Excel-files from your folder and then apply your query/function. As this request has nothing to do with your initial request I would propose to  mark my proposed solution as a solution to close this threat, you start out to understand your other requests you could have and open a new topic. Otherwise this here will be a never ending thread 🙂

Hope this is ok for you

Jimmy

[k_mathana]

Dear Jimmy,

Thank you for the response!

[Jimmy801 · Accepted solution]

Hello

but please accept my solution as solution, and not your answer

many thanks

Jimmy

[Greg_Deckler]

@k_mathana - Sounds like an utter nightmare Freddy Krueger style. Have fun @ImkeF and @edhans 

🙂

Yes, Krueger has 2 e's in it, I checked...