Power Query - Append 2 Tables

[Deppodi]

Hej!

 

På jobbet så får vi några gånger om dagen en säljrapport skickad till oss som visar försäljningen de senaste trettio dagarna. 

Med hjälp av power query och append two tables så skulle jag nu vilja kombinera all ny försäljningsdata med en gammal tabell. 

Jag har två tabeller, en "Old" och en "New". Problemet är att jag får med dubbletter av försäljningen och jag vet inte riktigt hur jag tar bort dubletterna på bästa sätt. 

 

Datan kan se ut såhär:

 

Varje produkt kommer på en egen rad. Som ni ser så kan Ordernummer #2 förekomma flera gånger baserat på hur många produkter den kunden har köpt. I det här fallet har ordernummer 2 köpt Äpple, Banan och Sko på en och samma order. 

I morgondagens rapport kommer ovan värden fortfarande att vara kvar, och den nya försäljning som har skett kommer att adderas. 

Lättaste lösningen som jag kunde komma på hade vart att använda sig av =match funktionen för att passa om ordernummer från "New" redan finns i "Old". I så fall vill jag att de ordrarna ska raderas från min "New", men jag vet inte riktigt hur jag skriver formeln i power query för att matcha? Det är första gången jag är inne i att skapa egna kolumner med formulas i Power Query, och de ser inte riktigt ut som vanliga formler..! Hjälp  

 

 

[MH_]

Kan du inte slå ihop 

https://learn.microsoft.com/sv-se/power-query/append-queries

och sedan ta bort dubbletter?

Markera alla kolumner i förhandsgranskningsläget. Högerklicka och väl "ta bort dubbletter"

Men det förutsätter förstås att du inte kan ha dubbletter inom samma ordernummer (typ 2 skor med samma ordernummer)

Går man in på 

Start->avancerad redigerare

blir den automatgenererade sammanslaging / dubblettrensningskoden någonting i stil med

let
    Källa = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
    #"Ändrad typ" = Table.TransformColumnTypes(Källa,{{"ID", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}}),
    #"Tillagd fråga" = Table.Combine({#"Ändrad typ", Sheet1}),
    #"Borttagna dubbletter" = Table.Distinct(#"Tillagd fråga")

in
    #"Borttagna dubbletter"

Där tabell1 är den ursprungliga huvudtabellen och "Sheet1" är frågan som hämtar dina uppdaterade data (t.ex från en mapp)

 

Se upp med "Table.Distinct" och kolla att den blir rätt

https://learn.microsoft.com/sv-se/powerquery-m/table-distinct

När jag körde först så kollade den bara dubbletter i min ID-kolumn. Typ:

#"Borttagna dubbletter" = Table.Distinct(#"Tillagd fråga", {"ID"})

 

Om man snabbläser "learn" länken så kan det vara så att du kan nöja dig med att kolla dubbletter det senaste dygnet. Men jag är inte säker.

Redigerad Juni 2, 2023 av MH_

[Deppodi]

Tack för snabbt svar @MH_, jag ska kika mer på din föreslagna lösning. 

Jag hittade ett sätt (tror jag) som fungerade. Fick lägga till en custom column med följande formel:

=if List.Contains(Old[Order_Code], [Order_Code]) then "" else "No"

Och efter det så körde jag en till edit för att rensa bort dubletterna. Om orderkoden redan fanns i old så returnerades null.  "Table.SelectRows(#"Added Custom", each [Match] <> null and [Match] <> "") för att ta bort null värden. 

På så sätt så returnerades alla dubbletter som redan fanns i old som null och raderades, samt att alla som inte var dubbletter markerades med "No" och flyttades in i min append mellan old och new. 

 

Dock tog det sjuuukt lång tid att köra den här power queryn. Jag vet inte om det brukar vara så? Jag klickade på refresh all vid 12:30 och den var klar nu vid 19:30. 

Mitt nya dokument innehåller 24988 rader, så det borde inte vara så stort? Kanske bara var första gången det tog lång tid att uppdatera tänker jag.. Fortsättning följer.. 

[Deppodi]

@MH_ Jag provade att göra som du föreslog, men jag tror inte att jag kan ta bort dubletter. Det som händer när jag gör en append mellan Old och New är att alla rader dubbleras. 

 

Order #2 Förekommer tre gånger i rapporten old. Eftersom att den kunden köpte tre olika produkter. Exempelvis banan, äpple, sko. 

Order #2 Förekommer tre gånger i rapporten ny. Eftersom att den kunden köpte tre olika produkter. Exempelvis banan, äpple, sko.

Jag kan inte ta bort dubletter på kolumnen ordernummer heller för då tar den bort två av tre rader. (Eller fem av sex rader efter append).

Ett alternativ är att jag bara uppdaterar den här datan på månadsbasis eller gör någon annan filtrerering innan jag gör en append. 

Någonting i stil med "Lägg bara till data från New om datumet är högre än 2023-05-15".

Min power query tar fortfarande lika lång tid att köra. Förstår inte varför det tar sån himla tid. Är mellan 6-8h timmar varje gång. 

Redigerad Juni 8, 2023 av Deppodi

[MH_]

Tidsåtgången är obegriplig. Kan inte hjälpa där. Läser du in tabell2 ordentligt innan du börjar joina?

 

Om du kollar länken

https://learn.microsoft.com/sv-se/powerquery-m/table-distinct

så hänvisar de till buffring som en lösning på vissa problem:

https://learn.microsoft.com/sv-se/powerquery-m/table-buffer

Men reservationen är talande

"om du använder den här funktionen kan det hända att dina frågor körs snabbare eller inte"

Men det kanske är värt att leka med i alla fall?

 

"Jag kan inte ta bort dubletter på kolumnen ordernummer heller"

Men om du rensar dubbletterna på alla kolumner borde det blir rätt. Eller?

dvs att du kör något i stil med

Table.Distinct(#"Tillagd fråga")
istället för 
Table.Distinct(#"Tillagd fråga", {"Ordernummer"})

Eller att du i alla fall lägger på både datum och produkt som filterkolumner

 

 

Testa att köra med formler istället?

Det här ger en rå sammanslagning av tabell1 och datadelen av tabell2

=VSTACK(Tabell1[#Alla];Tabell2)

Ta bort dubbletter (utan att ange någon kolumn= alla kolumner jämförs)

=UNIK(VSTACK(Tabell1[#Alla];Tabell2))

Eller så här om du bara vill ha ut data från tabell2 som är inlagt efter den sist inlagda i tabell1 (datum, ännu bättre om du har med tid. annars måste du vänta tills en dag är "färdig")

=VSTACK(Tabell1[#Alla];FILTER(Tabell2;Tabell2[Datum]>MAX(Tabell1[Datum])))

Problemet är naturligtvis att det sammanslagna hamnar i en ny tabell. Tabell1 är oförändrad. Så du måste en gång om dagen (ish) manuellt/script hämta hela den sammanslagna tabellen till tabell1 så att den innehåller allt gammalt innan du lägger på nya data.

 

Annat:

Jag är tyvärr inte inläst på "power automat" (menyn Automat i Din excel). Men jag tror att det finns färdiga funktioner för att göra precis det du vill. Eftersom "automat" inte har samma säkerhetsbegränsnngar som VBA så borde vem som helst få köra  det i din organisation.

Kolla om det finns något här- jag är som sagt var inte inläst och kan inte riktigt bedöma:

https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook

I exemplet handlar det nog (?) om att skapa ett automatiskt flöde i en onedrive -mapp ("automat" kan ställas in så att det körs automatiskt så fort något läggs till i en mapp, t.ex din rapport). Men även om dina filer ligger utanför onedrive så borde du kunna skapa en knapp så att du kan starta jobbet manuellt.

 

DAX 

distinct (union(bla bla, bla bla))

[Deppodi]

Tack @MH_ det fungerade fint med Table.Distinct! 

1) Power query mot old data
2) Power query mot new data
3) Append tables
4) = Table.Distinct(Source, {"ProduktID", "Ordernummer", "Orderdatum"})
5) Snygga till tabellen med lite filformat och skapa formler för att räkna ut priser mot inköpspriser

 

Tidsåtgången blev också mycket mer effektiv. Den här datan uppdaters på 30 minuter eller mindre. Måste ha vart någonting (formeln?) som gjorde att den förra power queryn blev riktigt tungkörd.. ett mysterium. 

Redigerad Juni 13, 2023 av Deppodi

[Deppodi]

Nu ska jag bara fundera lite på nästa problem.  

Power queryn hämtar och fyller på data från en mapp. I mappen slänger jag in senaste säljrapporten. 

Eftersom att min säljrapport innehåller försäljningen för de senaste trettio dagarna så måste jag komma på ett bra sätt för power queryn att lägga in alla nya datum som inte redan finns i "old" i rapporten.

 

Exempel:

Old innehåller försäljning för April

Säljrapporten nu innehåller försäljning för halva maj och halva Juni

= Jag får en lucka på halva maj. Försäljningen för första två veckorna i Maj är inte längre kvar i säljrapporten och jag har inte manuellt lagt in de i Old. 

 

Ett alternativ är att jag bestämmer mig för att lägga in rapporten i mappen med olika namn. Exempelvis "Säljrapport_W24" och nästa gång "Säljrapport_W25". Då löser Table.Distinct problemet. 

Men jag skulle helst vilja bara slänga in "Säljrapport" i mappen och skriva över den gamla excelfilen med samma namn. 

 

[MH_]

Något är fortfarande knas. Jag skulle uppfatta 30 sekunder som för mycket. 30 minuter är nonsens. 

*******************

Vet inte om jag fattar frågan. Om du inte har samkört "April" och skriver över filen så försvinner ju data?

*******************

 

> lägga in alla nya datum som inte redan finns i "old" i rapporten.

Du kan testa den här varianten från Ed Hansberry

https://www.ehansalytics.com/blog/2019/3/17/create-a-dynamic-date-table-in-power-query

gå ner till "So how do we make this dynamic? " för att se hans variant.

Förkortat:

I query editorn: Högerklicka på fråga1/old -whatever: Frågan som läser in "old"

Välj "referens"

Då skapas det en länk till fråga 1 (som heter Tabell1 i mitt påhittade exempel). Klickar du på "avancerad editor" ser du att koden är jättekort. Det är bara en länk till första frågan

let
    Källa = Tabell1
in
    Källa

 Döp om den nya frågan till "SistaDatum"

I den nya frågan Markerar du datumkolumnen och går till

Transformera->datum->Senaste

Den nya länk-frågan resultera då i en tabell med ett enda värde: det sista datumet i "OLD". Med en marginellt längre kod:

let
    Källa = Tabell1,
    #"Beräknat senast" = List.Max(Källa[Datum])
in
    #"Beräknat senast"

 

Sen behöver du "bara" filtrera bort gamla datum i tabell2 innan den skickas vidare till sammanslagning

Ed föreslår att du helt enkelt filtrerar tabell2 med ett godtyckligt datum och sedan ändrar koden

Gå till tabell2-frågan och klicka på "filterpilen" i kolumnhuvudet. Datumfilter, datum efter -> välj ett datum, vilket som

då får jag in en filterkod i stil med:

#"Filtrerade rader" = Table.SelectRows(#"Ändrad typ", each [Datum] > #date(2023, 6, 14))

Sen använder Ed frågan "SistaDatum" som en parameter och ersätter det "hårdkodade" datumet

    #"Filtrerade rader" = Table.SelectRows(#"Ändrad typ", each [Datum] > SistaDatum)

Sen använder du den filtrerade tabell2 när du slår ihop tabellerna.

 

*******************************************************************************

Du i princip skippa hjälptabellen och köra all kod i Fråga2. Men det var mycket svårare att få till en fungerande kod då (för mig). Men det här verkar funka.

#"Filtrerade rader" = Table.SelectRows(#"Ändrad typ", each [Datum] > List.Max(Tabell1[Datum]))

*******************************************************************************

*************
Tips
*************

Är du lika dålig som jag på att bestämma var tabellerna skall hamna?

I excelbladet, så syns frågorna till höger. Exempelvis behöver vi inte se tabellen "SistaDatum".

Högerklicka på frågan och välj "Läs in till"  och ändra till "skapa endast anslutning" (sen kan du ta bort det bladet också)

Du kan eventuellt göra samma sak för tabell2. Du behöver ju inte se den tabellen

Btw. Kör du "datamodellen"? Enligt min erfarenhet är det lite segare än klassisk excel. Om du inte behöver förmågan att hantera gigagantiska data så kanske du kan strunta i det.

**************************

Redigerad Juni 14, 2023 av MH_
Covfefe

[Deppodi]

Det här var superintressant! Tackar ödmjukast för förklaringen och länkarna. Väldigt uppskattat. Ping @MH_

Jag följde Eds steg för steg och fick koden att fungera samt se ut som hans. Blev såhär:

 

let
    Source = {Number.From(pmEarliestDate)..Number.From(pmLatestDate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Column1]), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Month Name", "MMM-YY", each Text.Start([Month Name],3) & "-" & Text.End(Text.From([Year]),2)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"MMM-YY", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MMM-YY Sort", each [Year]*100 + [Month]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MMM-YY Sort", Int64.Type}})
in
    #"Changed Type2"

 

Den här laddade snabbt, ungefär på en sekund!

Men.. Om jag har en rapport som heter alltid heter samma sak, som jag lägger in i samma mapp - då kommer den mappen alltid att innehålla försäljningen för de senaste 30 dagarna året runt. Låt oss kalla den excelfilen för "Säljrapport" så gör jag kopplingar till en mapp där jag alltid slänger in säljrapporten och skriver över den befintliga säljrapporten.

Kommer jag på något sätt att kunna få (Om jag har gjort rätt enligt guiden ovan) - min "Old" att dynamiskt uppdateras med ny data från "Säljrapport"? I Februari vill jag att den ska kolla om Februari finns i old och annars copy pasta all Februari data till old. I Mars vill jag att den ska kolla om mars data finns i old och annars copy pasta över den dit. 

Jag testade bara att leka lite nu med en egen excelfil, där jag hade en "Old" och en "New". Old innehöll fsg från Januari. New innehöll data från Februari. Sen gjorde jag en kopia på Februari och ändrade datumen i filen till Mars. Filen har samma filnamn hela tiden "New". När jag klistrar in "New" med mars data så skriver den över "New" med Februari data. Är tanken då att när Februari är det datumet som är i den senaste rapporten, att det ska flyttas över till "Old" och sparas där? Eller ska jag försöka kika på någon makrolösning för det istället? Query kanske inte är rätt väg att gå? 

Redigerad Juni 14, 2023 av Deppodi

[MH_]

Nja, du är nog tvungen att göra en flerstegsraket med tre filer inblandade.

Original.xlsx
Old.xlsx
New.xlsx

 

1. En fråga som hämtar dina originaldata till filen Old. Det är en powerQuery som du bara kör en gång.

Jag skapade en snabbfråga som hämtar Tabell1 i C:\tmp\Original.xlsx. Exempelfrågan blir så här: 

HämtaOriginal (läs in som tabell)

let
    Källa = Excel.Workbook(File.Contents("C:\tmp\Original.xlsx"), null, true),
    HämtaOriginal_tabell = Källa{[Item="Tabell1",Kind="Table"]}[Data],
    #"Ändrad typ" = Table.TransformColumnTypes(HämtaOriginal_tabell,{{"Datum", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}})
in
    #"Ändrad typ"

Stäng din Query och markera den skapade tabellen. Döp om tabellen till t.ex  "tabell_old" (menyn Tabelldesign->längst till vänster)

Sen skapar du en fråga som hämtar det sista datumet från Tabell_old

MaxDatum (Endast anslutning)

let
   Källa = Excel.CurrentWorkbook(){[Name="tabell_old"]}[Content],
    #"Beräknat senast" = List.Max(Källa[Datum])
in
    #"Beräknat senast"

Som du ser är det Tabell_old i "CurrentWorkbook" som används som källa. dvs den tabell som skapades när vi hämtade "original"

Sen hämtar du den nya tabellen och filtrerar den med hjälp av MaxDatum.

HämtaNew  (Endast anslutning)

let
    Källa = Excel.Workbook(File.Contents("C:\tmp\New.xlsx"), null, true),
    HämtaNew_Table = Källa{[Item="Tabell1",Kind="Table"]}[Data],
    #"Ändrad typ" = Table.TransformColumnTypes(HämtaNew_Table,{{"Datum", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    #"Filtrerade rader" = Table.SelectRows(#"Ändrad typ", each [Datum] > MaxDatum)
in
    #"Filtrerade rader"

Eller något annan av Ed's förslag

 

Slutligen slår du ihop frågan HämtaNew  och Tabell_old i "CurrentWorkbook". Ställ dig i "tabell_old" och gör en 

Data->hämta och transformera->från tabell/intervall

Fixa till koden så att det blir något i den här stilen: 

uppdatera_nytt

    Källa = Excel.CurrentWorkbook(){[Name="tabell_old"]}[Content],
    #"Tillagd fråga" = Table.Combine({Källa, HämtaNew})
in
    #"Tillagd fråga"

 

 

********* ed, glömde det viktigaste***********

Stäng editorn.  Dina data har lagts ihop i en ny tabell.  Radera den gamla "tabell_old"  och döp om din nya tabell till "tabell_old" (menyn Tabelldesign->längst till vänster)

Nu hämtas gamla data från Tabell_old och nya data läggs till Tabell_old. Dvs Tabell_old växer varje gång du läser in en ny rapport 

********************************************

 

Det är alltså frågan "uppdatera_nytt" som du kör när du vill lägga på nya data.

Testa på ett mindre material och se om det blir som du vill. 

 

Sen får du försöka optimera lite. Jag har kört på ett pyttexempel så jag vet inre riktigt om/vad som tar tid. Men "hämtaNew" och MaxDatum behöver inte synas som tabeller så du borde kunna köra dem som "endast anslutning":

Sen kan du högerklicka på varje fråga och kolla inställningar för anslutning osv.

Hämtningen av Original-tabellen behöver t.ex bara köras en gång (manuellt). så där kan du se till att alla uppdateringar är avstängda.

 

Eller- När du har kört Original-hämtningen så kan du ta bort den frågan om du vill

Ähhh. Testa med lite mindre filer som sagt var. Du vill väl framför allt vara säker på att du inte sätter igång en rundgång när tabellen uppdateras. 

Och så kanske du vill sortera efter datum osv 

/tjolavipp

Redigerad Juni 15, 2023 av MH_

[Deppodi]

Mycket intressant! Guld värt! Återkommer. 

[Deppodi]

Jaa!! Efter att ha prövat ungefär tio gånger och misslyckats så lyckades jag till slut 😁
Nu har jag också bara prövat med ett pyttelitet exempel som du nämnde. 
Nu ska jag fortsätta att pröva hur det här projektet fungerar med lite större data! 

Tack för hjälpen @MH_!!