PowerQuery concatenare dinamica

Doua add-onuri gratuite oferite de catre Microsoft ce completeaza functionalitatea Excel atat de bine incat incepend cu Excel 2013 Power Pivot vine preinstalat (doar trebuie activat) iar in Excel 2016 Power Query este inclus direct in interfata Excel (nu mai este add-on)
Tzica
Mesaje: 639
Membru din: Sâm Aug 11, 2012 10:52 pm

PowerQuery concatenare dinamica

Mesaj de Tzica » Mie Iul 01, 2020 12:32 pm

Buna ziua,
Primesc tabele cu nume, pe care le prelucrez in PQ ( eliminare diacritice, sedile, etc..).Deci AddColumn si...TextCombine(TextMatching) ..etc.. Dupa care trebuie sa le formatez gen functia Proper (excel) mai putin initiala care musai sa fie Upper case.(SplitColumns e ok).Dupa care trebuie sa le concatenez.Aici apare "problema".E posibil sa am nume cu cate 5 sau 6 nume.La concatenare, apare mesajul ca nu gaseste coloanele respective, dar intru si le sterg de manuta.Am gasit un cod pentru splituire dinamica ( sursa: https://markvsql.com/2015/03/advanced-c ... wer-query/ ) care mi-a dat speranta ca pot face automat/dinamic si concatenarea...prin Column names ..in interiorul aceleiasi interogari.Ei bine nu reusesc...sa scriu o instructiune care sa imi concateneze ( cu un spatiu) automat/dinamic coloanele care incep cu NP.1 , NP.2 ... NP.x , astfel incat sa nu mai intru sa le fac manual.
Multumesc.

LE: inteleg ca repectiva coloana nu face parte din tabelul sursa...si ca trebuie o transformare.Nu stapanesc M deloc
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Catalin B.
Moderator
Moderator
Mesaje: 813
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: PowerQuery concatenare dinamica

Mesaj de Catalin B. » Joi Iul 02, 2020 10:16 am

Buna ziua,
{"NP.1", "NP.2", "NP.3", "NP.4"}, care reprezinta lista de nume coloane ce trebuie combinate, se poate obtine dinamic in mai multe feluri.
De exemplu, se poate obtine prin extragerea numelor de coloana din etapa anterioara de procesare:
Table.ColumnNames(#"Uppercased Text")
Functia de mai sus returneaza lista completa de nume coloane:
{"NrCrt", "NITP", "CNP", "NP.1", "NP.2", "NP.3", "NP.4"}
Stiind ca primele 3 sunt statice, putem sa le indepartam pur si simplu:
List.RemoveItems(Table.ColumnNames(#"Uppercased Text"),{"NrCrt", "NITP", "CNP"})

Deci daca se inlocuieste in ultimul pas:
{"NP.1", "NP.2", "NP.3", "NP.4"} cu:
List.RemoveItems(Table.ColumnNames(#"Uppercased Text"),{"NrCrt", "NITP", "CNP"})
Rezultatul va fi dinamic, oricate coloane suplimentare NP.x vor fi.

Probleme vad mai multe:
1. nu doar concatenarea trebuie sa fie dinamica, ci si fiecare coloana NP trebuie transformata inainte de concatenare
2. Trim si Clean sunt aplicate pe fiecare coloana rezultata dupa Split, ceea ce e o greseala, care duce de fapt la problema 1 de mai sus.

Trim si Clean trebuie aplicate pe coloana NP inainte de split, pentru evitarea unui split INCORECT. (daca NP contine 2 spatii, Split va crea o coloana fara continut, deci apare o coloana inutila, la care Trim si Clean aplicat ulterior nu are nici un efect, mai mult, cand aceasta coloana va fi inclusa in Merged Columns, vor apare din nou 2 spatii consecutive)

Astfel, nu mai este necesara aplicarea Trim si Clean pe fiecare coloana in parte. Mai ramane Upper/Proper de aplicat, dar asta se poate face si ... fara Split!

Un exemplu:

Cod: Selectaţi tot

let
    Source = Excel.CurrentWorkbook(){[Name="SursaNume"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NrCrt", Int64.Type}, {"NITP", type text}, {"CNP", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NP", each Text.Combine( List.ReplaceMatchingItems(
   Text.ToList ( [NITP]) , {{"Ă", "A"} , {"Â" , "A"} , {"Î" , "I"} , {"Ș" , "S"}  , {"Ş" , "S"} , {"Ţ" , "T"} , {"Ț" , "T"} , {"-" , " "}}
))),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"NP", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"NP", Text.Clean, type text}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Cleaned Text",{{"NP", each Text.Split(_," "), type text}}),
    #"Trimmed Text2" = Table.TransformColumns(#"Trimmed Text1",{{"NP", (itm)=> List.TransformMany({0}, each List.Positions(itm), (x,y)=> if y=1 then Text.Upper(itm{y}) else Text.Proper(itm{y})), type text}}),
    #"Trimmed Text3" = Table.TransformColumns(#"Trimmed Text2",{{"NP", each Text.Combine(_," ") type text}})
in
    #"Trimmed Text3"
Mentionez ca cele 3 etape de Trim de dupa Cleaned Text le-am adaugat din power query editor, nu sunt scrise manual in M, am inlocuit formula Text.Trim cu alte formule.

Alt exemplu:

Cod: Selectaţi tot

let
    Source = Excel.CurrentWorkbook(){[Name="SursaNume"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NrCrt", Int64.Type}, {"NITP", type text}, {"CNP", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NP", each Text.Combine( List.ReplaceMatchingItems(
   Text.ToList ( [NITP]) , {{"Ă", "A"} , {"Â" , "A"} , {"Î" , "I"} , {"Ș" , "S"}  , {"Ş" , "S"} , {"Ţ" , "T"} , {"Ț" , "T"} , {"-" , " "}}
))),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"NP", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"NP", Text.Clean, type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Cleaned Text",{{"NP", Text.Proper, type text}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Capitalized Each Word",{{"NP", each Text.Split(_," "), type text}}),
    #"Added Custom1" = Table.AddColumn(#"Trimmed Text1", "Custom", each List.Combine({{[NP]{0},Text.Upper([NP]{1})},List.Skip([NP],2)})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Text.Combine([Custom]," "))
in
    #"Added Custom2"
Doar #"Added Custom1" este putin mai complicat, dar e foarte usor de inteles:
stiind ca [NP] contine acum o lista (am transformat in etapa anterioara [NP] din text in lista de cuvinte cu Split), iar {0}, {1} sunt elemente de pozitie in lista, reorganizam listele din coloana [NP] cum vrem:
lista 1: {[NP]{0},Text.Upper([NP]{1})} (luam primul element din lista asa cum e, adaugam al doilea element Upper case);
lista 2: List.Skip([NP],2) : din lista initiala [NP], eliminam primele 2 elemente, deoarece le-am folosit deja in lista 1;
Combinam cele 2 liste: List.Combine({Lista1,Lista2})

In etapa urmatoare, am transformat lista finala inapoi in text: #"Added Custom2" = each Text.Combine([Custom]," ")
Aici am adaugat o coloana separata, dar puteam la fel de bine sa mai aplic un Trim din interfata ;) , si sa schimb formula in formula bar:
ar fi rezultat un TransformColumns in loc de AddColumn:
#"Trimmed Text2 = Table.TransformColumns(#"Trimmed Text",{{"Custom", each Text.Combine(_," "), type text}}),

Ca de obicei, sunt mai multe metode de rezolvare, mai pot adauga n metode:
Dupa etapa Capitalized Each Word, se poate de exemplu adauga o coloana noua cu formula:

Cod: Selectaţi tot

List.Accumulate(Text.Split([NP]," "),"",(state,current)=>if List.PositionOf(Text.Split([NP]," "),current) =1 then state & " " & Text.Upper(current) else state & " " & current)
,
care nu face decat sa verifice pozitia fiecarui element din lista si sa converteasca doar elementul al doilea (listele au index in baza 0)
Probleme să fie, că soluţii se găsesc...

Tzica
Mesaje: 639
Membru din: Sâm Aug 11, 2012 10:52 pm

Re: PowerQuery concatenare dinamica

Mesaj de Tzica » Joi Iul 02, 2020 3:40 pm

Buna ziua,
Multumesc mult pentru solutiile oferite, precum si pentru explicatiile privind abordarea.
Am ales solutia unu, la care am adaugat un cod gasit in comunitatea PowerBI, pentru eliminarea spatiilor dintre cuvinte. ( Eu ma cramponasem pe ideea cu numaratul, pt ca o intelegeam mai usor..in fine).
Altele:
1.De acord, cu problemele semnalate ( coloane in plus)..dar nu ma deranjau ( setasem la ..10 !splituirea).Aplicam pe fiecare coloana Trim, pentru ca din cate am citit , functia respectiva elimina doar spatiile in plus de la sfarsit;
2.Am nevoie de coloana CNP, pentru ca il asociez cu tabelul in care am stocate..iban-urile (Ce -i drept nici eu nu am mentionat, ca respectiva operatiune e doar o parte din prelucrare).

Multumesc inca odata.
( imi permit sa pun excelul cu solutia (prima) oferita de CatalinB. in care am intercalat si eu ce am gasit pe net poate e de folos si altora).
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Catalin B.
Moderator
Moderator
Mesaje: 813
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: PowerQuery concatenare dinamica

Mesaj de Catalin B. » Joi Iul 02, 2020 4:05 pm

Cu placere.
Eu as fi mers pe ... ultima, care este echivalentul unui For Each...Next loop in visual basic. "state" este variabila care concateneaza rezultatele, si pleaca de la un string nul (""), fiecare valoare "current" se adauga la stringul "state". Valoarea "current" poate sa mai fie trecuta prin diverse functii suplimentare daca e nevoie, am folosit doar Upper/Proper.
Probleme să fie, că soluţii se găsesc...

Scrie răspuns

Înapoi la “Power Query & Power Pivot”