tabellen combineren met query

[Dick34]

Zie bijgaand voorbeeldbestand.

Tabel 1 is de tabel die ik gebruik voor genereren van overzichten enz. Deze tabel bevat cumulatieve aantallen per dag vanaf 1-1-2018. Van internet download ik periodiek een csv bestand met deze aantallen per dag vanaf 1-1-2020. Met een query laad ik de gegevens uit dat bestand in Tabel 2.

De nieuwe records (in het voorbeeld de records van de maand juni 2020) voeg ik met copy/paste toe aan Tabel 1.

Mijn vraag: Kan ik de ophaal query zo aanpassen dat de nieuwe records uit het csv-bestand direkt worden toegevoegd aan Tabel 1? Of is er een andere methode?

Met vriendelijke groet,
Dick

 

[JEC.]

Dat kan met Power Query.

Edit: Tabel staat op tabblad 2. Tabblad 1 kan weg

 

[Dick34]

Dank, ik begin me net te verdiepen in Power Query. Ik ben er nog lang niet uit. Wel kan ik zeggen dat de nieuwe tabel die je hebt gemaakt NIET goed is. De bedoeling is dat alleen de nieuwe records worden toegevoegd, nl. die van de maand juni.
In de totaal tabel zitten nu dubbele records, nl. die van 1-1 t/m 31-5-2020. Als het je lukt op dit op te lossen, is het dan mogelijk om de tekst van de gehele query te posten? Het bestandje dat je hebt gepost is beveiligd en verwijst ook nog naar bronnen op jouw computer, dus het werkt bij mij niet.
Met vriendelijke groet,
Dick

 

[JEC.]

Ik ga er nu vanuit dat je dan telkens de 2020 gegevens opnieuw inlaadt en wil laten overschrijven met de oude gegevens. Ik weet niet of ik de hele tekst van de query kan posten.

let
    Bron = Excel.Workbook(File.Contents("C:\Users\Veerman\Downloads\vb.xlsx"), null, true),
    Tabel2_Table = Bron{[Item="Tabel2",Kind="Table"]}[Data],
    #"Type gewijzigd" = Table.TransformColumnTypes(Tabel2_Table,{{"Datum", type date}, {"Aantal", Int64.Type}})
in
    #"Type gewijzigd"

Dit staat bij geavanceerde editor. Daar kun je je filepath aanpassen

 

[Dick34]

Het is gelukt me om met een query een NIEUWE tabel te maken die voldoet aan de voorwaarden (893 unieke records/rijen). Het is me niet gelukt om met een query de nieuwe records aan de oorspronkelijke tabel toe te voegen.

Met vriendelijke groet,
Dick

 

[JEC.]

Als je nu je de nieuwe data van 2020 in tabel 2 zet en de Power Query tabel ververst zou dat wel goed moeten gaan

 

[JEC.]

Probeer mijn bestand eens en vul een nieuwe waarde in bij 2020 in de oorspronkelijke tabel 2. Daarna sla je eerst op en ververs je de PQ tabel op het andere tabblad. Dan zie je dat de nieuwe waarde er bij komt

 

[Dick34]

Zoals ik al aangaf kan ik een nieuwe tabel maken die klopt. Maar wat ik wil is, dat de nieuwe records worden toegevoegd aan tabel 1. Vele andere tabellen, grafieken zijn nl. op tabel 1 gebaseerd.

 

[JEC.]

Wat je nu beschrijft is toch echt wat de Query doet. Of wil je echt dat de gegevens daadwerkelijk in de “.blauwe” tabel wordt geplaatst

 

[Dick34]

Ja dat bedoel ik. Immers, er zijn veel subtabellen en grafieken gebaseerd op de blauwe tabel 1. Die tabel moet dus voortdurend geupdate worden.

 

[JEC.]

Dan wordt het zoiets met VBA

Sub del()
With ListObjects(1).DataBodyRange
    .Columns(1).AutoFilter 1, ">=" & "1-1-2020"
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
With ListObjects(2).DataBodyRange
    .Copy ListObjects(1).ListRows.Add.Range
     ListObjects(1).DataBodyRange.AutoFilter
End With
End Sub

 

[Dick34]

Hallo,

Ik weet weinig tot niets van VBA, maar ik meende toch te begrijpen wat jouw progje doet: alle rijen met datum in 2020 in Tabel1 worden verwijderd en vervolgens worden alle rijen van Tabel2 toegevoegd aan Tabel1. Dat is precies wat ik wil.

Ik heb de vba-code getest in mijn oorspronkelijke bestand voorbeeld.xls. Eerst kreeg ik een foutmelding. Na wat gepuzzel (ik ben een VBA-leek) heb ik ActiveSheet geplaatst voor elke ListObjects en de Tabelnamen achter (niet elke) ListObjects geplaatst.
Toen werkte het!
De code wordt dus:

Sub del()
With ActiveSheet.ListObjects("Tabel1").DataBodyRange
.Columns(1).AutoFilter 1, ">=" & "1-1-2020"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
With ActiveSheet.ListObjects("Tabel2").DataBodyRange
.Copy ActiveSheet.ListObjects(1).ListRows.Add.Range
ActiveSheet.ListObjects(1).DataBodyRange.AutoFilter
End With
End Sub

Bedankt voor je hulp, het was me niet gelukt zonder!!
Ik moet het nog wel werkend zien te krijgen in mijn operationeel bestand. Of dat ook gaat lukken koppel ik terug.

Met vriendelijke groet,
Dick

 

[JEC.]

Oke mooizo! Gaat vast lukken. Probeer het eerst te testen op een een kopie van je bestand. VBA verwijdert de rijen en dat is niet terug te draaien

 

[HSV]

Ik zou het zo schrijven.

Zonder copy, en als er geen specialcells(12) zijn krijg je ook geen foutmelding.

Sub del()
 With ActiveSheet.ListObjects(1)
  .Range.AutoFilter 1, ">12-31-2019"
  Application.DisplayAlerts = False  'filterijen in een tabel verwijderen zonder melding
  .DataBodyRange.Offset(1).Delete
  .Range.AutoFilter
  .ListRows.Add.Range.Resize(ActiveSheet.ListObjects(2).ListRows.Count) = ActiveSheet.ListObjects(2).DataBodyRange.Value
 End With
End Sub

 

[Dick34]

@JVeer: ik heb je code geprobeerd op een kopie van mijn excelbestand, maar ik kreeg een foutcode in de regel met specialcells. Ik heb dat na een paar uren proberen niet kunnen oplossen. Ik ben toen maar weer overgestapt op kopiëren en plakken.

@HSV: ik zie net jouw suggestie. Dank daarvoor. Ga het in het weekend proberen.

Met vriendelijke groet,
Dick

 

[HSV]

Mag je het ook nog zo schrijven.

Sub del()
 With ActiveSheet.ListObjects(1)
  .Range.AutoFilter 1, ">12-31-2019"
  Application.DisplayAlerts = False  'filterijen in een tabel verwijderen zonder melding
  .Range.Offset(1).Delete
  .Range.AutoFilter
  .ListRows.Add.Range.Resize(ActiveSheet.ListObjects(2).ListRows.Count) = ActiveSheet.ListObjects(2).DataBodyRange.Value
 End With
End Sub

 

[Dick34]

@Harry,
Toch even geprobeerd, werkt wel, maar:
In mijn "echte" tabel staan formules. Voorbeeld: n kolom 3 heb in het jaartal opgehaald uit de datum en in kolom 4 de maand, en nog meer. De rijen van tabel 2 worden als waarden gekopieerd naar tabel 1, zodat ik de formules verlies.
Als ik copy/paste gebruik, blijven de formules intact.
Ik heb nog even naar de tabel commando's gekeken, maar niets gevonden.

Met vriendelijke groet,
Dick

 

[JEC.]

En wat als je het zo doet?

Sub del()
 With ActiveSheet.ListObjects(1)
  .Range.AutoFilter 1, ">12-31-2019"
  Application.DisplayAlerts = False  'filterijen in een tabel verwijderen zonder melding
  .Range.Offset(1).Delete
  .Range.AutoFilter
  .ListRows.Add.Range.Resize(ActiveSheet.ListObjects(2).ListRows.Count) = ActiveSheet.ListObjects(2).DataBodyRange.[COLOR="#FF0000"]Formula[/COLOR]
 End With
End Sub

 

[HSV]

Wat moet je nog met formules uit een ander tabel?; Het resultaat is hetzelfde.

 

[Dick34]

@JV: jouw idee was het eerste dat ik probeerde, maar er kwam een foutmelding.

@Harry en @JV: ik heb snel even een wat uitgebreider bestandje aangemaakt, met de macro "del" erin en met macro "value"die dus niet werkt.
Als ik alle rijen van tabel stroom_2020 kopieer en met plakken toevoeg aan tabel stroom, dan gaat het goed: de formules in "stroom" worden uitgevoerd. Misschien is de oplossing om de code zo aan te passen om alleen de eerste drie kolommen te plakken in tabel "stroom"? Ik zie (visueel) nl. bij de methode copy/paste 2 stappen: stap 1 de kolommen worden geplakt en stap 2 de formules in de overige kolommen worden berekend.

Met vriendelijke groet,
Dick