Two new columns different positions

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"

Hi @Frankee ,

Thank you for reaching out to the community.

While we wait for other members to share their insights, we’ve taken the liberty of using “Explain Simply ,” one of the tools available within Data Mentor (you can explore it here: https://mentor.enterprisedna.co/explain-simply . It generated the following results:.


Feel free to checkout more of our Data Mentor features as you work on your report. These tools are designed to help with tasks like the one you’re working on.

Cheers,

Enterprise DNA Support Team

Hi @Frankee,

I made some adjustments by hand, in the top half of your script. Let me know if you run into any issues, you can’t resolve and provide screenshots of error message and details - thank you

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/"
        )
    ), 
    #"Added Custom" = Table.AddColumn(
        #"Filtered Rows", 
        "InEight_Data", 
        each Excel.Workbook([Content])
    ), 
    #"Expanded InEight_Data" = Table.ExpandTableColumn(
        #"Added Custom", 
        "InEight_Data", 
        {"Name", "Data"}, 
        {"InEight_Data.Name", "InEight_Data.Data"}
    ), 
    #"Filtered Rows" = Table.SelectRows(
        #"Expanded InEight_Data", 
        each ([InEight_Data.Name] = "Formatted Data")
    ), 
    #"Removed Other Columns" = Table.SelectColumns(
        #"Filtered Rows", 
        {"Name", "InEight_Data.Data"}
    ), 
    #"Expanded InEight_Data.Data" = Table.Combine( 
        Table.AddColumn(
            #"Removed Other Columns", 
            "temp", 
            each Table.AddColumn( 
              Table.PromoteHeaders([#"InEight_Data.Data"], [PromoteAllScalars = true] ), 
              "_Name", 
              (x)=> [Name] 
            )
        )[temp]
    ),  
    #"Renamed Columns" = Table.RenameColumns(
        #"Expanded InEight_Data.Data", 
        {{Table.ColumnNames(#"Expanded InEight_Data.Data"){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"

I hope this is helpful

BTW Melissa , I’m reviewing the code a little closer and don’t see the two new columns incorporated.

Thanks again,

Frankee

Hello once again Melissa. appreciate the help .

I receive the following error @ Step “Filtered Row2”

Hi @Frankee,

Working blind, here. you’ll have to compare the output of #“Expanded InEight_Data.Data” in the adjusted query against the #“Promoted Headers” step in your original query.

To make column order irrelevant when combining tables, I nested the promotion of the header row. What is the purpose of #“Filtered Rows2”, is it still needed? The error message indicates the column “CBS Position Code” is missing. If needed, remove and rebuild that step, take it from there.

.

That’s correct. To review them, use the script below and focus on the step #“Review Expanded InEight_Data.Data” it is a redundant step and can be kept/removed, depending on your preference.

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/"
        )
    ), 
    #"Added Custom" = Table.AddColumn(
        #"Filtered Rows", 
        "InEight_Data", 
        each Excel.Workbook([Content])
    ), 
    #"Expanded InEight_Data" = Table.ExpandTableColumn(
        #"Added Custom", 
        "InEight_Data", 
        {"Name", "Data"}, 
        {"InEight_Data.Name", "InEight_Data.Data"}
    ), 
    #"Filtered Rows" = Table.SelectRows(
        #"Expanded InEight_Data", 
        each ([InEight_Data.Name] = "Formatted Data")
    ), 
    #"Removed Other Columns" = Table.SelectColumns(
        #"Filtered Rows", 
        {"Name", "InEight_Data.Data"}
    ), 
    #"Review Expanded InEight_Data.Data" = Table.AddColumn(
        #"Removed Other Columns", 
        "temp", 
        each Table.AddColumn( 
          Table.PromoteHeaders([#"InEight_Data.Data"], [PromoteAllScalars = true] ), 
          "_Name", 
          (x)=> [Name] 
        )
    ),
    #"Expanded InEight_Data.Data" = Table.Combine( 
        Table.AddColumn(
            #"Removed Other Columns", 
            "temp", 
            each Table.AddColumn( 
              Table.PromoteHeaders([#"InEight_Data.Data"], [PromoteAllScalars = true] ), 
              "_Name", 
              (x)=> [Name] 
            )
        )[temp]
    ),  
    #"Renamed Columns" = Table.RenameColumns(
        #"Expanded InEight_Data.Data", 
        {{Table.ColumnNames(#"Expanded InEight_Data.Data"){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"

If you need further assistance troubleshooting, I’d recommend sharing a mock-up file that includes the expected results.

I hope this is helpful

Melissa - I’m attaching a few files.
PBI sample,
3 Excel files old and new (w/ two new columns) and headers compared. old to new.

In the PBI you will find Power Query

  1. IE - works perfect…just missing the two new columns.

in these two below I attempted to add the columns using the following this YT video : @ about the 4:30 min.

  1. IE 2 - added after the promoted headers…closer to the end:

  2. IE 3 - added before promoted headers

In this pic you can see how the columns are staggered w/ the intro of two new columns:

thank you for any and all help.

Headers.xlsx (10.3 KB)
IE.pbix (116.7 KB)
Sub—0004E.xlsx (27.5 KB)
T-999—0008C-IE.xlsx (47.5 KB)

Hi @Frankee,

Thanks for sharing files, that is very helpful :+1:

First, setup a FolderLocation parameter, like so:
image

Make sure it is named = FolderLocation
Finally, create a new blank query and paste in this code:

let
    Source = Folder.Files(FolderLocation),  // Change the source to the local folder path
    FilterFiles = Table.SelectRows(
        Source, 
        each Text.StartsWith([Folder Path], FolderLocation) and [Extension] = ".xlsx"
    ), 
    PrepInEightData = Table.AddColumn(
        FilterFiles, 
        "InEight_Data", 
        each Table.SelectRows(Excel.Workbook([Content]), (x) => x[Name] = "Formatted Data")
    ), 
    #"Expanded InEightData" = Table.ExpandTableColumn(
        PrepInEightData, 
        "InEight_Data", 
        {"Name", "Data"}, 
        {"InEight_Data.Name", "InEight_Data.Data"}
    ), 
    PromoteNestedHeaders = Table.TransformColumns(
        #"Expanded InEightData", 
        {"InEight_Data.Data", each Table.PromoteHeaders(_, [PromoteAllScalars = true])}
    ), 
    SelectCols = Table.SelectColumns(PromoteNestedHeaders, {"Name", "InEight_Data.Data"}), 
    getHeaders = List.Distinct(
        List.Combine(List.Transform(SelectCols[InEight_Data.Data], Table.ColumnNames))
    ), 
    ExpandData = Table.ExpandTableColumn(SelectCols, "InEight_Data.Data", getHeaders), 
    RenameColumns = Table.RenameColumns(
        ExpandData, 
        {
            {Table.ColumnNames(ExpandData){0}, "InEight"}, 
            {"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"}
        }
    ), 
    SelectRows = Table.SelectRows(
        RenameColumns, 
        each (
            [#"CBS#(cr)#(lf)Position Code"]
                <> null
                and [#"CBS#(cr)#(lf)Position Code"] <> "CBS#(cr)#(lf)Position Code"
        )
            and ([Description] <> null)
    ), 
    FillDown = Table.FillDown(SelectRows, {"Craft", "WO", "SO", "Header", "Activity"}), 
    GroupedRows = Table.Group(
        FillDown, 
        {"InEight"}, 
        {{"Details", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}
    ), 
    #"Expanded Details" = Table.ExpandTableColumn(
        GroupedRows, 
        "Details", 
        List.Skip(Table.ColumnNames(GroupedRows[Details]{0}))
    ), 
    AddIndex = Table.AddIndexColumn(#"Expanded Details", "Index.1", 1, 1, Int64.Type), 
    ChType = Table.TransformColumnTypes(
        AddIndex, 
        {
            {"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
    ChType

I’ve consolidated some steps.
I hope this is helpful.

Melissa - this worked, perfectly. Attempted to rename “UserDefined 11” to TagNo, in this step:

= Table.RenameColumns(
        ExpandData, 
        {
            {Table.ColumnNames(ExpandData){0}, "InEight"}, 
            {"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"}, 
            {"User#(cr)#(lf)Defined 11", "TagNo"}
        }
    )

and received the following error:

![image|471x242](upload://7Rc7to2YZc8FYfnc9XWUK2l3OlS.png)


Thank you,

Frankee

@Frankee the image wasn’t uploaded properly.
Can you try that again?

image

The provided samples did not exhibit this issue, so possibly your production data differs slightly. To troubleshoot, try the following steps:

  1. Locate the “UserDefined 11” column in an earlier step.
  2. Temporarily rename the column at that spot. You can insert this action anywhere - as it will only be temporary.
  3. Copy the first string from the nested list that is generated.
  4. Remove the temporary renaming step, you have just inserted by clicking the X next to its name in the Applied Steps pane.
  5. Return to the RenameColumns step and replace the string with the one you copied earlier. This ensures that any hidden control characters, which might not have been present in the sample data, are now accounted for.

Note if this does not fix the issue, there is likely a difference in column names between files - in such a case you may want to remove all control characters from column names before consolidating data.

Table.TransformColumnNames can be used in such a scenario to clean all column names. Its incorporated here for illustration.

let
    Source = Folder.Files(FolderLocation),  // Change the source to the local folder path
    FilterFiles = Table.SelectRows(
        Source, 
        each Text.StartsWith([Folder Path], FolderLocation) and [Extension] = ".xlsx"
    ), 
    PrepInEightData = Table.AddColumn(
        FilterFiles, 
        "InEight_Data", 
        each Table.SelectRows(Excel.Workbook([Content]), (x) => x[Name] = "Formatted Data")
    ), 
    #"Expanded InEightData" = Table.ExpandTableColumn(
        PrepInEightData, 
        "InEight_Data", 
        {"Name", "Data"}, 
        {"InEight_Data.Name", "InEight_Data.Data"}
    ), 
    PromoteNestedHeaders = Table.TransformColumns(
        #"Expanded InEightData", 
        {
            "InEight_Data.Data", 
            each Table.TransformColumnNames(
                Table.PromoteHeaders(_, [PromoteAllScalars = true]), 
                Text.Clean
            )
        }
    ), 
    SelectCols = Table.SelectColumns(PromoteNestedHeaders, {"Name", "InEight_Data.Data"}), 
    getHeaders = List.Distinct(
        List.Combine(List.Transform(SelectCols[InEight_Data.Data], Table.ColumnNames))
    ), 
    ExpandData = Table.ExpandTableColumn(SelectCols, "InEight_Data.Data", getHeaders), 
    RenameColumns = Table.RenameColumns(
        ExpandData, 
        {
            {Table.ColumnNames(ExpandData){0}, "InEight"}, 
            {"UserDefined 3", "Craft"}, 
            {"UserDefined 4", "Header"}, 
            {"UserDefined 2", "Activity"}, 
            {"UserDefined 1", "WO"}, 
            {"UserDefined 10", "SO"}
        }
    ), 
    SelectRows = Table.SelectRows(
        RenameColumns, 
        each ([CBSPosition Code] <> null and [CBSPosition Code] <> "CBS Position Code")
            and ([Description] <> null)
    ), 
    FillDown = Table.FillDown(SelectRows, {"Craft", "WO", "SO", "Header", "Activity"}), 
    GroupedRows = Table.Group(
        FillDown, 
        {"InEight"}, 
        {{"Details", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}
    ), 
    #"Expanded Details" = Table.ExpandTableColumn(
        GroupedRows, 
        "Details", 
        List.Skip(Table.ColumnNames(GroupedRows[Details]{0}))
    ), 
    AddIndex = Table.AddIndexColumn(#"Expanded Details", "Index.1", 1, 1, Int64.Type), 
    ChType = Table.TransformColumnTypes(
        AddIndex, 
        {
            {"Unit Cost", type number}, 
            {"Total Cost(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(Total)", type number}, 
            {"Forecast(T/O) Quantity", Int64.Type}, 
            {"Index", Int64.Type}
        }
    )
in
    ChType

I hope this is helpful.