The following Power Query Works great for the old excel files w/ columns 1 thru 30.
Latest file introduced two new columns “User Defined 11” and “Takeoff Driver”. for a total of 32 columns.
The last column on both the old and the new files “WBS Elements” have the same name and are in two different positions. On the old file it’s the 30th column and on the new file it’s in the 32nd position.
I need to incorporate the two new columns. “User Defined 11” and “Takeoff Driver” and the “WBS Elements” column can move to the 32nd position or remain in the same position as long as no columns are duplicated.
I’ve read thru several topics and found none that worked.
Thank you in advance
let
Source = SharePoint.Files("https://web.sharepoint.com/teams/web-Chev/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://web.sharepoint.com/teams/web-Chev/Shared Documents/InEight/IE/")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "InEight_Data", each Excel.Workbook([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Name", "InEight_Data"}),
#"Expanded InEight_Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "InEight_Data", {"Name", "Data"}, {"InEight_Data.Name", "InEight_Data.Data"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded InEight_Data", each ([InEight_Data.Name] = "Formatted Data")),
#"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows1",{"Name", "InEight_Data.Data"}),
#"Expanded InEight_Data.Data" = Table.ExpandTableColumn(#"Removed Other Columns2", "InEight_Data.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32"}, {"InEight_Data.Data.Column1", "InEight_Data.Data.Column2", "InEight_Data.Data.Column3", "InEight_Data.Data.Column4", "InEight_Data.Data.Column5", "InEight_Data.Data.Column6", "InEight_Data.Data.Column7", "InEight_Data.Data.Column8", "InEight_Data.Data.Column9", "InEight_Data.Data.Column10", "InEight_Data.Data.Column11", "InEight_Data.Data.Column12", "InEight_Data.Data.Column13", "InEight_Data.Data.Column14", "InEight_Data.Data.Column15", "InEight_Data.Data.Column16", "InEight_Data.Data.Column17", "InEight_Data.Data.Column18", "InEight_Data.Data.Column19", "InEight_Data.Data.Column20", "InEight_Data.Data.Column21", "InEight_Data.Data.Column22", "InEight_Data.Data.Column23", "InEight_Data.Data.Column24", "InEight_Data.Data.Column25", "InEight_Data.Data.Column26", "InEight_Data.Data.Column27", "InEight_Data.Data.Column28", "InEight_Data.Data.Column29", "InEight_Data.Data.Column30", "InEight_Data.Data.Column31", "InEight_Data.Data.Column32"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded InEight_Data.Data", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(
#"Promoted Headers",
{
{Table.ColumnNames(#"Promoted Headers"){0}, "InEight"}
}
),
#"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each ([#"CBS#(cr)#(lf)Position Code"] <> null and [#"CBS#(cr)#(lf)Position Code"] <> "CBS#(cr)#(lf)Position Code") and ([Description] <> null)),
#"Filled Down" = Table.FillDown(#"Filtered Rows2",{"User#(cr)#(lf)Defined 3", "User#(cr)#(lf)Defined 1", "User#(cr)#(lf)Defined 10", "User#(cr)#(lf)Defined 4", "User#(cr)#(lf)Defined 2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Filled Down",{{"User#(cr)#(lf)Defined 3", "Craft"}, {"User#(cr)#(lf)Defined 4", "Header"}, {"User#(cr)#(lf)Defined 2", "Activity"}, {"User#(cr)#(lf)Defined 1", "WO"}, {"User#(cr)#(lf)Defined 10", "SO"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns1", {"InEight"}, {{"Details", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"CBS#(cr)#(lf)Position Code", "Description", "WBS: Arcadis#(cr)#(lf)(Arcadis Norms Activities)", "WBS: Arcadis#(cr)#(lf)(Arcadis Norms Activities) Description", "Forecast#(cr)#(lf)(T/O) Quantity", "Unit of#(cr)#(lf)Measure", "Contract Factor", "Man-Hours#(cr)#(lf)(Total)", "Labor Total Cost", "Materials Total Cost", "Rented Equipment Total Cost", "Owned Equipment Total Cost", "Total Cost#(cr)#(lf)(Forecast)", "Unit Cost", "Data#(cr)#(lf)Source", "Tag 6", "Man Count", "Tag 2", "Tag 4", "Tag 1", "Notes", "Craft", "Tag 3", "Last Changed#(cr)#(lf)On", "Last Changed#(cr)#(lf)By", "Header", "Activity", "WO", "SO", "WBS#(cr)#(lf)Element", "Index"}, {"CBS#(cr)#(lf)Position Code", "Description", "WBS: Arcadis#(cr)#(lf)(Arcadis Norms Activities)", "WBS: Arcadis#(cr)#(lf)(Arcadis Norms Activities) Description", "Forecast#(cr)#(lf)(T/O) Quantity", "Unit of#(cr)#(lf)Measure", "Contract Factor", "Man-Hours#(cr)#(lf)(Total)", "Labor Total Cost", "Materials Total Cost", "Rented Equipment Total Cost", "Owned Equipment Total Cost", "Total Cost#(cr)#(lf)(Forecast)", "Unit Cost", "Data#(cr)#(lf)Source", "Tag 6", "Man Count", "Tag 2", "Tag 4", "Tag 1", "Notes", "Craft", "Tag 3", "Last Changed#(cr)#(lf)On", "Last Changed#(cr)#(lf)By", "Header", "Activity", "WO", "SO", "WBS#(cr)#(lf)Element", "Index"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Details", "Index.1", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Unit Cost", type number}, {"Total Cost#(cr)#(lf)(Forecast)", type number}, {"Owned Equipment Total Cost", type number}, {"Rented Equipment Total Cost", type number}, {"Materials Total Cost", type number}, {"Labor Total Cost", type number}, {"Man-Hours#(cr)#(lf)(Total)", type number}, {"Forecast#(cr)#(lf)(T/O) Quantity", Int64.Type}, {"Index", Int64.Type}})
in
#"Changed Type"