Text to columns eller annan lösning?

[Deppodi]

Hej! 

 

Jag exporterar ut data från vårt affärssystem (SAP) och försöker analysera våra prisrader på olika kundgrupper. 
Problemet som jag har är att all data klumpas ihop i en enda cell.
Finns det något bra sätt för mig att bryta loss datan? Jag har provat med text to columns men jag tycker inte att det blir helt rätt ändå.
Datan som jag har att arbeta med ser ut såhär:

122.0 DKK:G::1 pieces::,105.0 DKK:G:ÄpplePriceGroup:1 pieces:[01.01.2022 00:00:00 to 08.05.2022 23:59:59]:,110.0 DKK:G:BanandkPriceGroup:1 pieces:[01.04.2022 00:59:59 to 26.04.2022 23:59:59]:,150.0 DKK:G:ApelsinKiwidkPriceGroup:1 pieces:[01.01.2022 00:00:00 to 08.05.2022 23:59:59]:,155.0 DKK:G:KiwidkPriceGroup:1 pieces:[01.04.2022 00:59:59 to 26.04.2022 23:59:59]:,160.0 DKK:G:BlåbärdkPriceGroup:1 pieces:[01.04.2022 00:59:59 to 26.04.2022 23:59:59]:

 

Jag skulle vilja att det såg ut såhär, på ett ungefär:

DKK:G 105,0 DKK ÄpplePriceGroup 01.01.2022 to 08.05.2022

DKK:G 110,0 DKK BanandkPriceGroup 01.04.2022 to 26.04.2022

[MH_]

dela upp på rader med ":," som radbrytare

=DELATEXT(A1;;":,")

eller t.om "]:,"

sen får du göra något liknande för att dela upp på kolumner med lämpliga avgränsare

=DELATEXT(B1;":")

och klistra ihop/radera som du vill

Men jag skulle titta på 

Data->hämta och transformera data.

då kan du göra en massa anpassningar och spara det som fråga

[Deppodi]

Tack för svar @MH_ 😊 Jag kom en bit på vägen med denna lösning, tror jag får försöka lite till. Lyckades lägga in det i powerquery men fick inte all data att delas upp som jag hade velat ha det. Blev ungefär som när jag använde den vanliga funktionen text till tal. Men visst borde det bli som du säger, att när jag är klar i power query och väljer close and load to, så är alla regler sparade för den typen av data. Så att det går fortare att göra det här steget nästa gång jag kommer in med samma mängd data? 

Det jag tycker är svårt med min fil ovan är att det är så vitt och brett på alla datum. 

 

Den här datan väljer jag att replace with ""

DKK:G::1 pieces::
1 pieces:
[
]
DKK:G:
DKK:G

Sen kommaavgräning som tecken som delar upp kolumnerna. Då blir min data uppdelad på sex kolumner, såhär:

 

122.0, 105.0 ÄpplePriceGroup01.01.2022 00:00:00 to 08.05.2022 23:59:59:, 110.0 BanandkPriceGroup01.04.2022 00:59:59 to 26.04.2022 23:59:59:, 150.0 ApelsinKiwidkPriceGroup01.01.2022 00:00:00 to 08.05.2022 23:59:59:, 155.0 KiwidkPriceGroup01.04.2022 00:59:59 to 26.04.2022 23:59:59: och 160.0 BlåbärdkPriceGroup01.04.2022 00:59:59 to 26.04.2022 23:59:59:.

 

105, ÄpplePriceGroup, 01.01.2022, 08.05.2022 är ju det jag vill få ut från kolumn 2. Så jag missar nåt. 

[MH_]

om vi kör formler (enklare att skriva på nätet) så kan du lägga in allt du vill använda som delare i en matris:

{",";" to ";"DKK:G:";":1 pieces:[";":1 pieces:[";"]"}

Svårt att läsa, men det är 5 saker:

{",";
" to ";
"DKK:G:";
":1 pieces:[";
;"]"}

eller så här om man petar in det i formeln:

=DELATEXT(A1;{",";" to ";"DKK:G:";":1 pieces:[";"]"};":,")

Då blir det väl ungefär som du vill? I alla fall om du formaterar datumkolumnerna utan tidsvisning

[MH_]

Power Query (exempel om dina data ligger i en cell - allt utom starten är likadant om du importerar direkt från en textfil)

Markera cellen med dina data i. Gå till

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

Nu har du förmodligen bara en cell i powerquery's förhandsgranskning. Gå till 

Start->dela upp kolumn: efter avgränsare

Ändra "välj eller ändra avgränsare" till "anpassad" och klistra in avgränsaren ":,"  (utan citattecken)

Sen klickar du på "avancerade alternativ"  och ändra "dela i" till "rader"

Nu bör du ha 6 rader. 

Upprepa "dela på kolumn"

Start->dela upp kolumn: efter avgränsare 

Men klistra in "DKK:G:" istället (utan citat-tecken) och kolla så att avancerad står på "kolumn"

Markera din nya kolumn 2 och upprepa "dela upp kolumn" men med

":1 pieces:[" (utan citat)

Sen tar du din nya kolumn 3 och delar på blanksteg (finns att välja)

Om datumen har konverterats till svenska datum så kan du kolla på "kodlistan" till höger och ta bort den sista "ändra typ"

Radera tids-kolumnerna (om du inte vill ha dom, annars tar du bara bort sista hakparantesen "]"). Du kan även ta bort första raden om den alltid är nonsens. Och kanske ge vettiga kolumnnamn

Stäng och läs in

 

PS

Om du vill konvertera datumen från text kan du högerklicka och välja "ändra typ". Tyvärr kan du inte välja "datum" direkt eftersom queryn kommer att utgå ifrån att det är amerikanska datum. Du får istället välja "använd språkinställningar" längst ner och välja "datum" och något europeiskt land (t.ex engelska: europa)

PS2

När du står i frågeredigeraren så kan du gå till 

START->avancerad redigerare och kolla på din frågekod. Så här blev det för mig (alla namn är automatskapade och jag har tagit de automatiska typändingarna)

let
    Källa = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
    #"Dela upp kolumn efter avgränsare" = Table.ExpandListColumn(Table.TransformColumns(Källa, {{"Kolumn1", Splitter.SplitTextByDelimiter(":,", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Kolumn1"),
    #"Dela upp kolumn efter avgränsare1" = Table.SplitColumn(#"Dela upp kolumn efter avgränsare", "Kolumn1", Splitter.SplitTextByDelimiter("DKK:G:", QuoteStyle.Csv), {"Kolumn1.1", "Kolumn1.2"}),
    #"Dela upp kolumn efter avgränsare2" = Table.SplitColumn(#"Dela upp kolumn efter avgränsare1", "Kolumn1.2", Splitter.SplitTextByDelimiter(":1 pieces:[", QuoteStyle.Csv), {"Kolumn1.2.1", "Kolumn1.2.2"}),
    #"Dela upp kolumn efter avgränsare3" = Table.SplitColumn(#"Dela upp kolumn efter avgränsare2", "Kolumn1.2.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Kolumn1.2.2.1", "Kolumn1.2.2.2", "Kolumn1.2.2.3", "Kolumn1.2.2.4", "Kolumn1.2.2.5"}),
    #"Borttagna kolumner" = Table.RemoveColumns(#"Dela upp kolumn efter avgränsare3",{"Kolumn1.2.2.2", "Kolumn1.2.2.3", "Kolumn1.2.2.5"}),
    #"Ändrad typ med nationella inställningar" = Table.TransformColumnTypes(#"Borttagna kolumner", {{"Kolumn1.2.2.1", type date}, {"Kolumn1.2.2.4", type date}}, "en-150"),
    #"Borttagna topprader" = Table.Skip(#"Ändrad typ med nationella inställningar",1)
in
    #"Borttagna topprader"

 

Redigerad Oktober 25, 2022 av MH_

[Deppodi]

Wow vilket svar! 😄Jag är Såå tacksam för att du sätter dig in i min problematik och hjälper till. Det här ska jag testa en gång till! Tack!

[MH_]

Då kan du ju klura på om det här funkar (samma logik som powerQueryn)...

=INDEX(DELATEXT(A1;{"DKK:G:";":1 pieces:[";" "};":,");SEKVENS(ANTALV(DELATEXT(A1;":,")));{1,3,4,7})

eller om den här är bättre

=INDEX(DELATEXT(A1;{"DKK:G:";":1 pieces:[";" "};":,");SEKVENS(ANTALV(DELATEXT(A1;":,"))-1;;2);{1,3,4,7})

Queryn är rätt smidig när man ser det på det viset...

[Deppodi]

@MH_ Det var bra att du delade frågekoden. Jag fick inte till det helt 100% när jag gjorde det manuellt med power query, men i och med att källkoden gick att copy-paste så blev det exakt som jag ville! Håller onekligen med om att query är väldigt smidigt för att skapa regler. Ligger på min to-do att bli duktig på!

Redigerad November 8, 2022 av Deppodi