Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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=?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
yes i want to keep my exiting file as it is and append new one
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Sorry, my bad. Missed the adjustment of the sheet-name (from "Tabelle2" to "Master"). Corrected it above, pls try again
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
where is that . Can you tell me ? I coul not find it
I changed the second step, please try the code again.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |