Dynamic header name change - Power Query

[exactpro]

Hi

I have attached zipped folder with one subfolder where source data is and main report file where query resides

Datafiles folder has individual subcontractors T&A CSV files with company name header on top of each file

I am trying to create a dynamic T&A report with Pivot table here, files in datafiles folder suppose to be replaceable but since I have used ALPHA file when setting up query and steps, name change of column ALPHA (or say any other contractor) is hard coded which breaks when you have another named subcontractor lets say BETA

I have tried transposing and retransposing back but it didn't work as dataset is dynamic and number of columns will vary when you transpose first time (rows will be columns and they are varying for each period and file)

So I am assuming that I will need some custom M Code to get around this column header change issue

Please help

Thanks
Prash

 

[MarcelBeug]

The easiest way is to do the rename just after headers are promoted and the subcontractor column is the last column in the table.

    #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows4", [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers1",{{List.Last(Table.ColumnNames(#"Promoted Headers1")), "Contractor Name"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Total"}),

 

[MarcelBeug]

Otherwise I would do all the transformations in the query Transform Sample File from t1 g4s ...:

let
    Source = Csv.Document(#"Sample File Parameter1",[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Contractor = Text.Middle(Table.FirstValue(Source),14),
    #"Removed Top Rows" = Table.Skip(Source,5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Personnel Serial] <> "")),
    AddedContractor = Table.AddColumn(#"Filtered Rows", "Contractor Name", each Contractor, type text),
    #"Renamed Columns" = Table.RenameColumns(AddedContractor,{{"Surname", "Employee Name"}, {"Minutes", "Hours:MM"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Hours:MM", Int64.Type}}, "en-GB"),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text),
    Duration = Table.TransformColumns(#"Inserted Day Name",{{"Hours:MM", each #duration(0,0,_,0)}}),
    #"Inserted Day" = Table.AddColumn(Duration, "Day", each Date.Day([Date]), type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Day",{"Contractor Name", "Payroll Number", "Employee Name", "Date", "Day Name", "Hours:MM", "Day"})
in
    #"Removed Other Columns"

... and limit the main query to:

let
    Source = Folder.Files(FilePath&DataFolder),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform File from t1 g4s", each #"Transform File from t1 g4s"([Content]), Value.Type(#"Transform Sample File from t1 g4s")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from t1 g4s"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from t1 g4s", Table.ColumnNames(#"Removed Other Columns1"[#"Transform File from t1 g4s"]{0}))
in
    #"Expanded Table Column1"

Notice the addition Value.Type(#"Transform Sample File from t1 g4s") in the #"Invoke Custom Function1" step: this will take over the data types from the sample query, so no data typing will be required any more after expanding the table column.