Reapply PQ steps to another table (slightly different columns)

[ilcaa]

i have 6 tables, the Date field is all in Column 1, i created a transform on this date field since it comes in as Text. I want to apply the same transform on the other 5 tables since the 1st column is exactly the same. The transformation works on the other tables EXCEPT the last step, which is the reordering of the date field as the first column. This last step fails since there are slightly different column names

i tried using wildcards or leaving the other columns names out in the last step but it doenst work (moves to first column). Any ideas?

let
    Source = Sql.Databases("SL01P"),
    Validation = Source{[Name="Validation"]}[Data],
    dbo_ValidationReport_AccountHierarchy = Wallet_Validation{[Schema="dbo",Item="ValidationReport_AccountHierarchy"]}[Data],
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(dbo_ValidationReport_AccountHierarchy, {{"PeriodID", type text}}, "en-US"), "PeriodID", Splitter.SplitTextByPositions({0, 4}, false), {"PeriodID.1", "PeriodID.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"PeriodID.1", Int64.Type}, {"PeriodID.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Number.ToText([PeriodID.2])&"/"&Number.ToText([PeriodID.1])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"PeriodID.1", "PeriodID.2"}),
[B]    #[/B]  [B]  #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "BankID", "Open_Accounts", "New_Open_Accounts", "Existing_Open_Accounts"})[/B]

in
    #"Reordered Columns"

 

[El Cid]

After #"Removed Columns" =,
Demote the headers
Rename the headers the way you want them
Demote headers again
Transpose
Delete Column 3 that has the old names
Transpose
Promote the headers
Reordered columns
I'm sure someone else might have a more elegant solution.

 

[ilcaa]

thanks Cid,

but i just want to move my new Date column to the first column automatically but cant because of this last "Reordered Columns", so doing everything you mentioned is manual and more work then just leaving out last row and manually moving to first column. i though using wildcards or something in the code would work but it doesnt..

 

[El Cid]

My solution is a brute force fix as long as all of the columns in each table have the same data. Once you do the code for the first file, you can copy-paste it to the others in the same place. This article might lead you to a better approach: https://blog.crossjoin.co.uk/2014/1...excel-workbooks-with-power-querythe-easy-way/

 

[Bill Szysz]

Your last step....

 #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns", {"Date"} & List.RemoveLastN(Table.ColumnNames( #"Removed Columns"), 1) )

 

[El Cid]

Bill da PQ Man 2 da rescue!

Bill you need to change your profile pic: http://assets.natgeotv.com/Shows/21982.jpg

 

[ilcaa]

Hi Bill, thanks that worked!!