Espandere in contemporanea tutte le colonne presenti L’argomento è risolto

[marcoli]

Salve a tutti dopo aver visto alcune lezioni di Power Query su You tube un guru' mi ha consigliato di iscrivermi a questo forum che non conoscevo

Avrei necessità di sapere se esiste la possibilità di inserire un comando detto ExpandAll ossia che una volta creata la connessione alla query all interno di Power Query ,e aver visualizzato le colonne " madri " mi possa automaticamente aprire in contemporanea tutte le sotto colonne esistenti nei file che file che voglio analizzare
Facendolo manualmente cliccando sulla freccia destra/sinistra di ogni colonna le sotto colonne si aprono ed è quello che serve a me ma essendo svariate vorrei trovare una scorciatoia , nella libreria del linguaggio M non ho trovato nulla ma ammetto di essere ignorante in materia

Potreste darmi consigli , grazie in anticipo Marco

[Andrea90]

Ciao Marco,

se sei interessato a leggere un articolo in inglese puoi cominciare a guardare questo del grande Chris Webb.

https://www.google.co.uk/amp/s/blog.cro ... query/amp/

La funzione che dici tu è una funzione custom che devi creare in linguaggio M per poi usarla con la tua base dati per effettuare in automatico l espansione delle tabelle sottostanti.

Se vuoi incominciare a darci un’occhiata poi nel caso non sia ben chiaro puoi ritornare qui magari inserendo un file di esempio.

A presto,

Andrea

[marcoli]

Ciao ho letto l' articolo che mi hai cortesemente messo in link , sinceramente non sono esperto di linguaggi ( credevo sinceramente che ci fosse un comando predisposto a far quello che chiedo ) ho comunque recuperato questa funzione ma non ho capito se deve essere modificata a seconda del numero di colonne esistenti e sopratutto dove va inserita .Leggendo un post dove veniva scritto il comando ExpandAll dal pulsante " funzione " doveva funzionare ma a me non lo fa ..credo perchè il linguaggio scritto qui in allegato deve essere prima inserito in memoria da qualche parte ma con ignoranza dal sito che mi hai linkato non lo capisco

Aiuto !

Codice: Seleziona tutto

let
    //Define function taking two parameters - a table and an optional column number 
    Source = (TableToExpand as table, optional ColumnNumber as number) =>
    let
     //If the column number is missing, make it 0
     ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
     //Find the column name relating to the column number
     ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
     //Get a list containing all of the values in the column
     ColumnContents = Table.Column(TableToExpand, ColumnName),
     //Iterate over each value in the column and then
     //If the value is of type table get a list of all of the columns in the table
     //Then get a distinct list of all of these column names
     ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, 
                        each if _ is table then Table.ColumnNames(_) else {}))),
     //Append the original column name to the front of each of these column names
     NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
     //Is there anything to expand in this column?
     CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
     //If this column can be expanded, then expand it
     ExpandedTable = if CanExpandCurrentColumn 
                         then 
                         Table.ExpandTableColumn(TableToExpand, ColumnName, 
                                ColumnsToExpand, NewColumnNames) 
                         else 
                         TableToExpand,
     //If the column has been expanded then keep the column number the same, otherwise add one to it
     NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
     //If the column number is now greater than the number of columns in the table
     //Then return the table as it is
     //Else call the ExpandAll function recursively with the expanded table
     OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) 
                        then 
                        ExpandedTable 
                        else 
                        ExpandAll(ExpandedTable, NextColumnNumber)
    in
     OutputTable
in
    Source

[Andrea90]

Hai modo di allegare il file contenente l origine dati che vorresti espandere?

A presto,

Andrea

[marcoli]

Ciao posso riuscire a mandare questa immagine

come si vede si sono 2 colonne da espandere che a loro volta contengono nuove colonne da espandere

Cosa si puo' fare ?

[Enrico Galli]

Ciao marcoli. Sfortunatamente, se non alleghi un file di excel, tutto quello che possiamo fare è spiegarti come procedere, ma poi devi metterci del tuo, perché non si tratta di fare due click e via.

Il codice che hai postato devi metterlo in una nuova query e chiamarla ExpandAll (parti da nuova query => altre origini => query vuota), poi devi creare un nuovo passaggio successivo nella tua query di consolidamento (quella che ci mostri in foto). Mettiamo che l'ultimo passaggio si chiami #"Modificato tipo": il nuovo passaggio che vai a creare dovrà avere questo comando:
Espansione = ExpandAll(#"Modificato tipo")
Se scarichi il file di esempio dall'articolo che ti ha segnalato Andrea, la query ExpandAll c'è già: puoi cliccare col tasto destro sull'elenco delle query e fare copia-incolla nel tuo file

[marcoli]

Ciao a tutti ecco un file prova basta cliccare e si apre l' editor
Sono dati sensibili ma se risolvere è troppo importante! Se mi fate vedere i passaggi dove inserire l' espansione automatica sarei grato

Grazie in anticipo Marco

[Andrea90]

Ciao Marco,

Non ti preoccupare che nessuno vede i tuoi dati visto che il file che hai inserito ha un caricamento da cartella e senza il contenuto della stessa (il file che vuoi analizzare) per noi non è utilizzabile.

Pertanto ti ho scaricato da internet un file xml a caso che ti allego assieme al file excel che contiene la query di tuo interesse.

Basta che indichi all'interno del file excel nel foglio "FileEsempio" il percorso completo (con anche il nome del file) che conterrà il file xml di prova che ti giro. (per ora il percorso completo è settato con il mio pc).

Una volta fatto questo vai nel primo foglio e con il tasto destro premi sul comando "aggiorna".

A caricamento effettuato puoi entrare dentro i passaggi della query e vedere la formula per l'ExpandAll, ed il suo utilizzo per estrapolare i dati di una colonna del file d'esempio.

A presto,

Andrea

[marcoli]

Salve Andrea scusa per l errore, volevo inserire più file derivanti da una cartella perché questo è quello che mi succede nella realta'. Ho provato a capire ma mi manca qualche intuizione nel percorso che hai fatto. Io vado su Power gli dico carica da cartella e poi modifica e così si apre l editor,clicco su combina file ( i due puntini) e così mi si aprono le principali colonne, credo che e' qui che devo inserire la funzione di espansione ma mi mancano i punti dove immettere la funzione . La path e' stata rinominata e cliccando su funzione ExpandAll del tuo file da il risultato visualizzato nel collegato file Excel ma a me manca l ABC per l inserimento attuato al mio file. Il video in inglese sopra postato non e' per me di facile comprensione! Puoi girarmi i vari passaggi tramite immagini? Un video sarebbe chiedere troppo!! Grazie Marco

[Andrea90]

Ciao Marco,

se vuoi aggiungere la funzione personalizzata devi fare cosi:

Dal tab "Home" seleziona "nuova origine" --> "altre origini" --> query vuota



Ora cambia il nome alla Query, di default ti risulterà col nominativo: Query1



Invece di Query1 scrivi ExpandAll

Premi il comando di Invio

Poi vai sul tab "Visualizza" e seleziona "Editor Avanzato", ti si aprirà una finestra dal quale dovrai togliere tutto ed al suo posto incollare questo codice:

Codice: Seleziona tutto

let
    Source = (TableToExpand as table, optional ColumnNumber as number) =>
    let
     ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
     ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
     ColumnContents = Table.Column(TableToExpand, ColumnName),
     ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
     NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
     CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
     ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
     NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
     OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else ExpandAll(ExpandedTable, NextColumnNumber)
    in
     OutputTable
in
    Source

A questo punto dai conferma e vedrai che ti comparirà il simbolo della funzione accanto al nome (dalla lista delle queries posta a sinistra).

Adesso torna nella tua query principale.

Avrai una colonna che contiene dei dati "Table".

Accedi al tab "Aggiungi colonna" e premi il comando "Richiama funzione personalizzata"



Ora seleziona dal menu a tendina chiamato "Query della funzione" la funzione ExpandAll

Poi selezionando il pulsante evidenziato sotto seleziona l'opzione "Nome colonna"



Poi dal menu a tendina a fianco seleziona il nome della colonna che contiene i dati in formato Tabella.

Premi Ok

A questo punto avrai una nuova colonna con dei dati in formato tabella, ma completamente espansi al suo interno. L'unica cosa che ti rimane da fare è cliccare sul solito pulsante per l'espansione della tabella e premere conferma. (puoi togliere il flag dall'opzione "usa il nome della tabella originale come prefisso".



A presto,

Andrea