Dynamic header name change - Power Query

exactpro

exactpro

New member
Joined
Jun 12, 2017
Messages
1
Reaction score
0
Points
0
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
 

Attachments

  • T&A_Report_0517.zip
    261.7 KB · Views: 44
The easiest way is to do the rename just after headers are promoted and the subcontractor column is the last column in the table.

Code: 
    #"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"}),
 
Otherwise I would do all the transformations in the query Transform Sample File from t1 g4s ...:

Code: 
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:

Code: 
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.
 
You must log in or register to reply here.
Back
Top