How to chnage file type in power bi ?

[Sac]

Hello Everybody,

I have already upload my excel file , perfom all DAX expression for analytics and make a useful dashboard.  After end of the job, i realise that i have to keep my data up-to-date.

Now, i want to load data from folder so when new file come in the same folder, power bi automatically connet to my exiting data and keep the data up to date.

Is it possible to change my file excel to folder in my current exiting project ?  and How ?

Otherwise i have to load the data and make the project again from scratch. please help me  

[ImkeF]

Do you "only" want to connect to a new file (with a new name) in the folder or do you need to connect to multiple files (and append them in an additional step so that they result in one table still=?

[Sac]

yes i want to keep my  exiting file as it is and append  new one

[ImkeF]

You have to replace the first rows of your code. Could you please send first 5 rows of your code? (From the advanced editor)

Thx

[Sac]

Sure. Here it is,

let
    Source = Excel.Workbook(File.Contents("\\v173p176a\sNARAYANBHAI$\DATA\My Redirected Folders\Desktop\Komatsu Suppiler & Finance Data\DHL Control Expenses 2017.xlsx"), null, true),
    Master_Sheet = Source{[Item="Master",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Master_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Wk", Int64.Type}, {"Account", Int64.Type}, {"Invoice", type text}, {"Date", type date}, {"Origin Code", type text}, {"Origin Text", type text}, {"Airway Bill", Int64.Type}, {"Shipper Reference", type any}, {"Charge Description", type text}, {"Destination Code", type text}, {"Destination Text", type text}, {"Product", type text}, {"Weight", type number}, {"Weight Type", type text}, {"Pieces", Int64.Type}, {"Freight Charge", type number}, {"Discount", Int64.Type}, {"Tax Charge", Int64.Type}, {"Net Charge", type number}, {"Sender Name", type text}, {" Receiver Name", type text}, {"Destination", type text}, {"Receiving Branch", type any}, {"Branch - Invoice", type text}, {"Branch - Cost Centre / GL Code / Intl Code", type text}, {"Country of Origin", type text}, {"Country of Destination", type text}, {"Trade", type text}, {"Zone", Int64.Type}, {"Old-PERTH", Int64.Type}, {"New-PERTH", Int64.Type}, {"Over/Duties", Int64.Type}, {"Old-Export", Int64.Type}, {"Old-Import", Int64.Type}, {"New-Export", type number}, {"New-Import", Int64.Type}, {"Third Party", Int64.Type}, {"Fuel", type any}, {"Package", type any}, {"Match", type any}, {"Doc-EXP", type number}, {"Doc-IMP", Int64.Type}, {"Document", type number}, {"Match_1", type text}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Rate Check", type any}, {"Ratecard", type text}, {"OVERALL", type any}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year.1", each Date.Year([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Split Column by Position" = Table.SplitColumn(#"Inserted Month Name", "Month Name", Splitter.SplitTextByPositions({0, 3}, false), {"Month Name.1", "Month Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month Name.1", type text}, {"Month Name.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Month Name.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Year.1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Month/Year", each [Month Name.1]&" "&[Year.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Rate Check", "Ratecard", "OVERALL"}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns1", {"Fuel", "Package", "Match"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Errors", "Sender Country", each if [Origin Code] = "BNE" then "AUSTRALIA" else if [Origin Code] = "PER" then "AUSTRALIA" else if [Origin Code] = "SYD" then "AUSTRALIA" else if [Origin Code] = "MEM" then "AMERICA" else if [Origin Code] = "ORD" then "AMERICA" else if [Origin Code] = "TYO" then "JAPAN" else "OTHER" ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Reveiver Country", each if [Destination Code] = "ADL" then "AUSTRALIA" else if [Destination Code] = "BNE" then "AUSTRALIA" else if [Destination Code] = "CNS" then "AUSTRALIA" else if [Destination Code] = "DRW" then "AUSTRALIA" else if [Destination Code] = "MBW" then "AUSTRALIA" else if [Destination Code] = "MEL" then "AUSTRALIA" else if [Destination Code] = "NRT" then "AUSTRALIA" else if [Destination Code] = "NSW" then "AUSTRALIA" else if [Destination Code] = "PER" then "AUSTRALIA" else if [Destination Code] = "RQL" then "AUSTRALIA" else if [Destination Code] = "SYD" then "AUSTRALIA" else if [Destination Code] = "XXF" then "AUSTRALIA" else if [Destination Code] = "TYO" then "JAPAN" else "OTHER" )
in
    #"Added Conditional Column1"

[ImkeF]

This should work:

let
    Source = Folder.Files("\\v173p176a\sNARAYANBHAI$\DATA\My Redirected Folders\Desktop\Komatsu Suppiler & Finance Data"),
    #"Added Custom0" = Table.AddColumn(Source, "Custom0", each Table.PromoteHeaders(Excel.Workbook([Content]){[Item="Master",Kind="Sheet"]}[Data], [PromoteAllScalars=true])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom0",{"Name", "Custom0"}),
    #"Expanded Custom0" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom0", Table.ColumnNames(#"Removed Other Columns"[Custom0]{0})),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom0",{{"Wk", Int64.Type}, {"Account", Int64.Type}, {"Invoice", type text}, {"Date", type date}, {"Origin Code", type text}, {"Origin Text", type text}, {"Airway Bill", Int64.Type}, {"Shipper Reference", type any}, {"Charge Description", type text}, {"Destination Code", type text}, {"Destination Text", type text}, {"Product", type text}, {"Weight", type number}, {"Weight Type", type text}, {"Pieces", Int64.Type}, {"Freight Charge", type number}, {"Discount", Int64.Type}, {"Tax Charge", Int64.Type}, {"Net Charge", type number}, {"Sender Name", type text}, {" Receiver Name", type text}, {"Destination", type text}, {"Receiving Branch", type any}, {"Branch - Invoice", type text}, {"Branch - Cost Centre / GL Code / Intl Code", type text}, {"Country of Origin", type text}, {"Country of Destination", type text}, {"Trade", type text}, {"Zone", Int64.Type}, {"Old-PERTH", Int64.Type}, {"New-PERTH", Int64.Type}, {"Over/Duties", Int64.Type}, {"Old-Export", Int64.Type}, {"Old-Import", Int64.Type}, {"New-Export", type number}, {"New-Import", Int64.Type}, {"Third Party", Int64.Type}, {"Fuel", type any}, {"Package", type any}, {"Match", type any}, {"Doc-EXP", type number}, {"Doc-IMP", Int64.Type}, {"Document", type number}, {"Match_1", type text}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Rate Check", type any}, {"Ratecard", type text}, {"OVERALL", type any}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year.1", each Date.Year([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Split Column by Position" = Table.SplitColumn(#"Inserted Month Name", "Month Name", Splitter.SplitTextByPositions({0, 3}, false), {"Month Name.1", "Month Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month Name.1", type text}, {"Month Name.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Month Name.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Year.1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Month/Year", each [Month Name.1]&" "&[Year.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Rate Check", "Ratecard", "OVERALL"}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns1", {"Fuel", "Package", "Match"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Errors", "Sender Country", each if [Origin Code] = "BNE" then "AUSTRALIA" else if [Origin Code] = "PER" then "AUSTRALIA" else if [Origin Code] = "SYD" then "AUSTRALIA" else if [Origin Code] = "MEM" then "AMERICA" else if [Origin Code] = "ORD" then "AMERICA" else if [Origin Code] = "TYO" then "JAPAN" else "OTHER" ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Reveiver Country", each if [Destination Code] = "ADL" then "AUSTRALIA" else if [Destination Code] = "BNE" then "AUSTRALIA" else if [Destination Code] = "CNS" then "AUSTRALIA" else if [Destination Code] = "DRW" then "AUSTRALIA" else if [Destination Code] = "MBW" then "AUSTRALIA" else if [Destination Code] = "MEL" then "AUSTRALIA" else if [Destination Code] = "NRT" then "AUSTRALIA" else if [Destination Code] = "NSW" then "AUSTRALIA" else if [Destination Code] = "PER" then "AUSTRALIA" else if [Destination Code] = "RQL" then "AUSTRALIA" else if [Destination Code] = "SYD" then "AUSTRALIA" else if [Destination Code] = "XXF" then "AUSTRALIA" else if [Destination Code] = "TYO" then "JAPAN" else "OTHER" )
in
    #"Added Conditional Column1"

[Sac]

I just paste it in  but it show me this error

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=Record
    Table=Table

[ImkeF]

Sorry, my bad. Missed the adjustment of the sheet-name (from "Tabelle2" to "Master"). Corrected it above, pls try again

[Sac]

where is that . Can you tell me ?  I coul not find it 

[ImkeF]

I changed the second step, please try the code again.