Forum Discussion
New Excel columns in Power Query
I import data into PBI from an Excel file loaded into Sharepoint. New fileds added to the Excel file do not show in Power Query. There are many threads saying that you need to go into Advanced Editor and change the column value to the new number of columns in your updated Excel file. However I do not see that line of code in my Advanced Editor.
Any advice would be appreciated.
Marc
Hi MarcUrdang
click this
for example,
Any question, please let me know. Looking forward to receiving your reply.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
MarcUrdang , In the recent version of power bi for excel and SQL server database, it has picked up new columns automatically.
Prior to that for excel we use to go and add columns to the list.
Can you share the code which are having in advance editor
refer if needed
How to manage when column is added or removed at source: https://www.youtube.com/watch?v=h0yLtceT0i4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=2
Hi ... this is the code...
let
Source = SharePoint.Files("https://dsvcorp.sharepoint.com/teams/ZAReportingViewingPlatform", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://dsvcorp.sharepoint.com/teams/ZAReportingViewingPlatform/Shared Documents/Refresh Files/Specific - used for unique outputs/PUMA/Audit & Review/PUMA Airfreight/")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (11)", each #"Transform File (11)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File (11)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (11)", Table.ColumnNames(#"Transform File (11)"(#"Sample File (11)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"House Bill", type text}, {"Job Number", type text}, {"House Bill Origin", type text}, {"House Bill Origin Country", type text}, {"House Bill Destination", type text}, {"House Bill Destination Country", type text}, {"Shipper Organization", Int64.Type}, {"Shipper Name", type text}, {"Consignee Org#", Int64.Type}, {"Consignee Name", type text}, {"Transport Mode", type text}, {"Branch", type text}, {"Department", type text}, {"Actual Chargeable", type text}, {"UM", type text}, {"Invoice Date", type date}, {"Invoice", Int64.Type}, {"Currency", type text}, {"FOB Value", type text}, {"Air FRT", type text}, {"Air DES", type text}, {"Air ORG", type text}, {"Air DTY & ADV", type text}, {"Air VAT & AGI", type text}, {"Group Total", type text}, {"Total Tax", type text}, {"FOB %", Percentage.Type}, {"FOB % (Excl. VAT)", Percentage.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Invoice Date", "Invoice Date - Copy"),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Invoice Date - Copy", type text}}, "en-ZA"), "Invoice Date - Copy", Splitter.SplitTextByPositions({0, 2}, true), {"Invoice Date - Copy.1", "Invoice Date - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Invoice Date - Copy.1", type text}, {"Invoice Date - Copy.2", Int64.Type}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Invoice Date - Copy.1", Splitter.SplitTextByPositions({0, 4}, true), {"Invoice Date - Copy.1.1", "Invoice Date - Copy.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Invoice Date - Copy.1.1", Int64.Type}, {"Invoice Date - Copy.1.2", type text}}),
#"Split Column by Position2" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type2", {{"Invoice Date - Copy.1.1", type text}}, "en-ZA"), "Invoice Date - Copy.1.1", Splitter.SplitTextByPositions({0, 2}, true), {"Invoice Date - Copy.1.1.1", "Invoice Date - Copy.1.1.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position2",{{"Invoice Date - Copy.1.1.1", Int64.Type}, {"Invoice Date - Copy.1.1.2", Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"House Bill", "Job Number", "House Bill Origin", "House Bill Origin Country", "House Bill Destination", "House Bill Destination Country", "Shipper Organization", "Shipper Name", "Consignee Org#", "Consignee Name", "Transport Mode", "Branch", "Department", "Actual Chargeable", "UM", "Invoice Date", "Invoice", "Currency", "FOB Value", "Air FRT", "Air DES", "Air ORG", "Air DTY & ADV", "Air VAT & AGI", "Group Total", "Total Tax", "FOB %", "FOB % (Excl. VAT)", "Invoice Date - Copy.1.1.2", "Invoice Date - Copy.1.2", "Invoice Date - Copy.1.1.1", "Invoice Date - Copy.2"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Invoice Date - Copy.1.1.1", type text}, {"Invoice Date - Copy.2", type text}}, "en-ZA"),{"Invoice Date - Copy.1.1.1", "Invoice Date - Copy.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"House Bill", "Job Number", "House Bill Origin", "House Bill Origin Country", "House Bill Destination", "House Bill Destination Country", "Shipper Organization", "Shipper Name", "Consignee Org#", "Consignee Name", "Transport Mode", "Branch", "Department", "Actual Chargeable", "UM", "Invoice Date", "Invoice", "Currency", "FOB Value", "Air FRT", "Air DES", "Air ORG", "Air DTY & ADV", "Air VAT & AGI", "Group Total", "Total Tax", "FOB %", "FOB % (Excl. VAT)", "Merged", "Invoice Date - Copy.1.2", "Invoice Date - Copy.1.1.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns1","/","",Replacer.ReplaceText,{"Invoice Date - Copy.1.2"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Invoice Date - Copy.1.1.2", type text}}, "en-ZA"),{"Merged", "Invoice Date - Copy.1.2", "Invoice Date - Copy.1.1.2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Invoice Date USE"),
#"Replaced Value1" = Table.ReplaceValue(#"Merged Columns1","//","",Replacer.ReplaceText,{"Invoice Date USE"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value1",{{"Invoice Date USE", type date}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type4",",","",Replacer.ReplaceText,{"FOB Value", "Air FRT", "Air DES", "Air ORG", "Air DTY & ADV", "Air VAT & AGI", "Group Total", "Total Tax"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",".",",",Replacer.ReplaceText,{"FOB Value", "Air FRT", "Air DES", "Air ORG", "Air DTY & ADV", "Air VAT & AGI", "Group Total", "Total Tax"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Replaced Value3",{{"Total Tax", type number}, {"Group Total", type number}, {"Air VAT & AGI", type number}, {"Air DTY & ADV", type number}, {"Air ORG", type number}, {"Air DES", type number}, {"Air FRT", type number}, {"FOB Value", type number}, {"Invoice", type text}}),
#"Replaced Value4" = Table.ReplaceValue(#"Changed Type5",",","",Replacer.ReplaceText,{"Actual Chargeable"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",".",",",Replacer.ReplaceText,{"Actual Chargeable"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Replaced Value5",{{"Actual Chargeable", type number}})
in
#"Changed Type6"
Hi MarcUrdang
click this
for example,
Any question, please let me know. Looking forward to receiving your reply.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
