VBA Recording Power Query not working

Good afternoon everyone

I am having a problem with Recording a Marco with Power Query

I set up a Excel sheet with Power query in Office 365 working at home
As my work only runs office 2013 I need to know change all the code over I have set up both Office 365 and Office 2013 on my home PC correctly using office 2013

I have been able to set up all my data and tables with naming them all when I do a Marco record same as I did it on office 365 its not saving any of the PQ code

My steps are open sheet Transport click on Cell A1
click Power Query From Table/Range Power Query opens
Click Advanced Editor replace all code with below

let
    Source = Excel.CurrentWorkbook(){[Name="Datetransport"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "Truck Number", "Time#(lf)Slot", "Arrival Time", "Load Start Time", "Load Finish Time ", "Paperwork To Driver", "Uni Carrier", "Carrier", "Load Number 1", "Load Number 2", "Load Number 3", "Load Number 4", "Load Number 5", "Load Number 6", "Load Number 7", "Load Number 8", "Load Number 9", "Load Number 10", "Load Number 11", "Load Number 12", "Load Number 13", "Load Number 14", "Load Number 15", "Load Number 16", "Load Number 17", "Load Number 18", "Load Number 19", "Load Number 20", "Load Number 21", "Load Number 22", "Load Number 23", "Load Number 24", "Load Number 25", "Load Number 26", "Load Number 27", "Load Number 28", "Load Number 29", "Load Number 30", "Comments"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Date", "Truck Number", "Time#(lf)Slot", "Arrival Time", "Load Start Time", "Load Finish Time ", "Paperwork To Driver", "Uni Carrier", "Carrier", "Comments"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Value] <> null and [Value] <> ""),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Time#(lf)Slot", type time}, {"Arrival Time", type time}, {"Load Start Time", type time}, {"Load Finish Time ", type time}, {"Paperwork To Driver", type time}, {"Value", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Truck Number", "Date", "Value", "Uni Carrier", "Time#(lf)Slot", "Arrival Time", "Load Start Time", "Load Finish Time ", "Paperwork To Driver", "Carrier", "Comments"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Load Number"}, {"Uni Carrier", "Destination Location Name"}, {"Load Start Time", "Commence Loading"}, {"Load Finish Time ", "Complete Loading"}})
in
    #"Renamed Columns"

Close and load to table existing worksheet =Transport!CV1 then load

everything works

when I try and do the same with recording Macro at the end my vba only shows

    Sheets("Transport").Select
    Range("Datetransport[[#Headers],[Date]]").Select
    Selection.AutoFilter
    ActiveSheet.ListObjects("Datetransport_2").Resize Range("$CV$1:$DF$84")
    Range("CV2").Select
End Sub

when I used office 365 it looked like below

Any help would be great Still learning how to Code in VBA and use record a lot

Sub V3DatePW()
'

    
    Sheets("Transport").Select
    Range("Datetransport[[#Headers],[Date]]").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Queries.Add Name:="Datetransport", Formula:= _
        "let" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Datetransport""]}[Content]," & Chr(10) & "    #""Removed Other Columns"" = Table.SelectColumns(Source,{""Date"", ""Truck Number"", ""Booked Time"", ""Arrival Time"", ""Load Start Time"", ""Load Finish Time "", ""Paperwork to Driver"", ""Uni Carrier"", ""Carrier"", ""Load Number 1"", ""Load Number 2"", ""Load Number 3"", ""Load Numb" & _
        "er 4"", ""Load Number 5"", ""Load Number 6"", ""Load Number 7"", ""Load Number 8"", ""Load Number 9"", ""Load Number 10"", ""Load Number 11"", ""Load Number 12"", ""Load Number 13"", ""Load Number 14"", ""Load Number 15"", ""Load Number 16"", ""Load Number 17"", ""Load Number 18"", ""Load Number 19"", ""Load Number 20"", ""Load Number 21"", ""Load Number 22"", ""Loa" & _
        "d Number 23"", ""Load Number 24"", ""Load Number 25"", ""Load Number 26"", ""Load Number 27"", ""Load Number 28"", ""Load Number 29"", ""Load Number 30"", ""Comments""})," & Chr(10) & "    #""Unpivoted Columns"" = Table.UnpivotOtherColumns(#""Removed Other Columns"", {""Date"", ""Truck Number"", ""Booked Time"", ""Arrival Time"", ""Load Start Time"", ""Load Finish Time "", ""Pape" & _
        "rwork to Driver"", ""Uni Carrier"", ""Carrier"", ""Comments""}, ""Attribute"", ""Value"")," & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Unpivoted Columns"",{""Attribute""})," & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Removed Columns"", each [Value] <> null and [Value] <> """")," & Chr(10) & "    #""Reordered Columns"" = Table.ReorderColumns(#""Filtered Rows"",{""Truck Numb" & _
        "er"", ""Date"", ""Value"", ""Uni Carrier"", ""Booked Time"", ""Arrival Time"", ""Load Start Time"", ""Load Finish Time "", ""Paperwork to Driver"", ""Carrier"", ""Comments""})," & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Reordered Columns"",{{""Date"", type date}, {""Value"", type text}, {""Booked Time"", type time}, {""Arrival Time"", type time}, {""Load " & _
        "Start Time"", type time}, {""Load Finish Time "", type time}, {""Paperwork to Driver"", type time}})," & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Changed Type"",{{""Value"", ""Load number""}, {""Uni Carrier"", ""Destination Location Name""}, {""Arrival Time"", ""Arrive Dispatch""}, {""Load Start Time"", ""Commence Loading""}, {""Load Finish Time "", ""Complete" & _
        " Loading""}, {""Paperwork to Driver"", ""Collect Paperwork""}})" & Chr(10) & "in" & Chr(10) & "    #""Renamed Columns"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Datetransport;Extended Properties=""""" _
        , Destination:=Range("$A$700")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Datetransport]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Datetransport_2"
        .Refresh BackgroundQuery:=False
        Sheets("Process").Select
        Range("A1").Select
    End With
End Sub

Good afternoon AliGW

The main reason I went to VBA is to set up a Marco so other staff members at work would just need to click a few buttons and it would all work

I did not know PQ routines can be automated I did a search on google and it points me to office 365 ;-(

You help me a few months ago with pointing me to PQ which has been great learning heaps

A different any help would be helpful

After trying to work this out for the last few hours with setting up all the correct PQ
I have 4 PQ codes that I like to run
first 3 PQ get information from tables sort rename and remove unneeded columns
the 4th one mergers everything in a set way


P.s
sorry what do you mean by PQ code is cammed "M"

AliGW

I think this is a Limit on office 2013

I just tried the same code

On the same excel sheet tried office 365 the same way with record macro and worked

I was reading up on https://stackoverflow.com/questions/...r-query-in-vba

I now get what you mean by PQ code is cammed "M"