bat01

Sub PQueryPPivot()
    '
    ' Macro1 Macro
    '

    '
        'tabel comenzi
        ActiveWorkbook.Queries.Add Name:="comenzi", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Users\user\OneDrive - EXCELp.ro\Desktop\_curs PowerExcel PORSCHE nov2021 (1)\_curs PowerExcel PORSCHE nov2021\aFiles\comenzi.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    CapColoana = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    EliminareColoane" & _
            " = Table.SelectColumns(CapColoana,{""Data comanda"", ""Data transport"", ""Client ID"", ""Canal"", ""Depozit"", ""Destinatie ID"", ""Regiune ID"", ""Cantitate"", ""Pret unitar"", ""Cost unitar""})," & Chr(13) & "" & Chr(10) & "    TipDate = Table.TransformColumnTypes(EliminareColoane,{{""Data comanda"", type date}, {""Data transport"", type date}, {""Client ID"", Int64.Type}, {""Canal"", type " & _
            "text}, {""Depozit"", type text}, {""Destinatie ID"", Int64.Type}, {""Regiune ID"", Int64.Type}, {""Cantitate"", Int64.Type}, {""Pret unitar"", type number}, {""Cost unitar"", type number}})," & Chr(13) & "" & Chr(10) & "    PretLinie = Table.AddColumn(TipDate, ""Pret Linie"", each [Cantitate] * [Pret unitar], type number)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    PretLinie" & _
            ""
        ThisWorkbook.Connections.Add2 "Query - comenzi", _
            "Connection to the 'comenzi' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=comenzi;Extended Properties=""""" _
            , "SELECT * FROM [comenzi]", 2

        'tabel regiuni

        ActiveWorkbook.Queries.Add Name:="Regiuni", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Users\user\OneDrive - EXCELp.ro\Desktop\_curs PowerExcel PORSCHE nov2021 (1)\_curs PowerExcel PORSCHE nov2021\aFiles\baza.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Regiuni_Sheet = Source{[Item=""Regiuni"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Regiuni_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Ch" & _
            "anged Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""ID Regiune"", Int64.Type}, {""Localitate"", type text}, {""Judet"", type text}, {""Judet FD"", type text}, {""Cod judet"", type text}, {""Regiune"", type text}, {""Adresa"", type text}, {""Sector"", type text}, {""Populatie"", Int64.Type}, {""Desitate pop"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        ThisWorkbook.Connections.Add2 "Query - Regiuni", _
            "Connection to the 'Regiuni' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Regiuni;Extended Properties=""""" _
            , "SELECT * FROM [Regiuni]", 2

        'merge tabele
        ActiveWorkbook.Queries.Add Name:="Merge1", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Table.NestedJoin(comenzi, {""Destinatie ID""}, Regiuni, {""ID Regiune""}, ""Regiuni"", JoinKind.LeftOuter)," & Chr(13) & "" & Chr(10) & "    #""Expanded Regiuni"" = Table.ExpandTableColumn(Source, ""Regiuni"", {""Localitate"", ""Judet"", ""Judet FD"", ""Cod judet"", ""Regiune"", ""Adresa"", ""Sector"", ""Populatie"", ""Desitate pop""}, {""Localitate"", ""Judet"", ""Judet FD""" & _
            ", ""Cod judet"", ""Regiune"", ""Adresa"", ""Sector"", ""Populatie"", ""Desitate pop""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Expanded Regiuni"""
        ThisWorkbook.Connections.Add2 "Query - Merge1", _
            "Connection to the 'Merge1' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Merge1;Extended Properties=""""" _
            , "SELECT * FROM [Merge1]", 2

        'pivot
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
            ActiveWorkbook.Connections("Query - Merge1"), Version:=7).CreatePivotTable _
            TableDestination:="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion _
            :=7
        Cells(1, 1).Select
        With ActiveSheet.PivotTables("PivotTable1")
            .ColumnGrand = True
            .HasAutoFormat = True
            .DisplayErrorString = False
            .DisplayNullString = True
            .EnableDrilldown = True
            .ErrorString = ""
            .MergeLabels = False
            .NullString = ""
            .PageFieldOrder = 2
            .PageFieldWrapCount = 0
            .PreserveFormatting = True
            .RowGrand = True
            .SaveData = True
            .PrintTitles = False
            .RepeatItemsOnEachPrintedPage = True
            .TotalsAnnotation = False
            .CompactRowIndent = 1
            .InGridDropZones = False
            .DisplayFieldCaptions = True
            .DisplayMemberPropertyTooltips = False
            .DisplayContextTooltips = True
            .ShowDrillIndicators = True
            .PrintDrillIndicators = False
            .AllowMultipleFilters = False
            .SortUsingCustomLists = True
            .FieldListSortAscending = True
            .ShowValuesRow = False
            .CalculatedMembersInFilters = False
            .RowAxisLayout xlCompactRow
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotCache
            .RefreshOnFileOpen = False
            .MissingItemsLimit = xlMissingItemsDefault
        End With
        ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
        'adauga coloane
        ActiveSheet.PivotTables("PivotTable1").Name = "Pivot"
        With ActiveSheet.PivotTables("Pivot").PivotFields("Canal")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveSheet.PivotTables("Pivot").AddDataField ActiveSheet.PivotTables("Pivot"). _
            PivotFields("Cantitate"), "Sum of Cantitate", xlSum
        ActiveSheet.PivotTables("Pivot").AddDataField ActiveSheet.PivotTables("Pivot"). _
            PivotFields("Pret Linie"), "Sum of Pret Linie", xlSum
        'formatare numere
        Columns("A:C").Select
        Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

        'adauga grafic
        Range("A1").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$C$5")
        ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
        ActiveChart.FullSeriesCollection(1).AxisGroup = 1
        ActiveChart.FullSeriesCollection(2).ChartType = xlLine
        ActiveChart.FullSeriesCollection(2).AxisGroup = 1
        ActiveChart.FullSeriesCollection(1).ChartType = xlLine
        ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
        ActiveChart.FullSeriesCollection(1).AxisGroup = 2
        ActiveSheet.Shapes("Chart 1").IncrementLeft -95.25
        ActiveSheet.Shapes("Chart 1").IncrementTop -29.25
        ActiveSheet.Shapes("Chart 1").IncrementLeft 330.75
        ActiveSheet.Shapes("Chart 1").IncrementTop 24.75
        Range("A2").Select
        With ActiveSheet.PivotTables("Pivot").PivotFields("Regiune")
            .Orientation = xlRowField
            .Position = 2
        End With
        'schimba numele foi cu pivot
        Sheets("Sheet1").Select
        Sheets("Sheet1").Name = "Pivot"
        'sorteaza pivot
        ActiveSheet.PivotTables("Pivot").PivotFields("Regiune").AutoSort xlDescending, _
            "Sum of Pret Linie", ActiveSheet.PivotTables("Pivot").PivotColumnAxis. _
            PivotLines(2), 1
        Range("C2").Select
        ActiveSheet.PivotTables("Pivot").PivotFields("Canal").AutoSort xlAscending, _
            "Sum of Pret Linie", ActiveSheet.PivotTables("Pivot").PivotColumnAxis. _
            PivotLines(2), 1
        'schimba locatia graficului
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Grafic"

        'adauga filtru in pivot
        Sheets("Pivot").Select
        With ActiveSheet.PivotTables("Pivot").PivotFields("Depozit")
            .Orientation = xlPageField
            .Position = 1
        End With
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").ClearAllFilters
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").CurrentPage = "AXW291"
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").ClearAllFilters
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").CurrentPage = "FLR025"
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").ClearAllFilters
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").CurrentPage = "GUT930"
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").ClearAllFilters
        ActiveSheet.PivotTables("Pivot").PivotFields("Depozit").CurrentPage = "NXH382"

    End Sub