Questa settimana ho avuto una situazione in cui ho bisogno di creare una tabella di budget in Power Pivot. Ho avuto la possibilità di caricare un budget settimanale come un singolo record per negozio e utilizzando DAX per calcolare i budget dall'inizio dell'anno, o caricare una serie di record (uno per ogni settimana) in una tabella di bilancio. Ho scelto quest'ultimo in questo caso come mi ha dato l'opportunità di testare nuove competenze in Power Query. Ho ricreato lo scenario qui sotto.
Budget settimanali per negozio
Ho un foglio di calcolo che contiene il budget per negozio a settimana. Il budget è lo stesso per ogni settimana per ogni negozio per la successiva 15 settimane. La tabella del budget si presenta così: facile inserire i dati ma non ottimale per PowerPivot. Nel mio esempio di vita reale, c'erano molte più colonne per Contrazione, GP e alcune altre metriche sulla pagina.
Le schede lunghe e strette di solito sono migliori per Power Pivot, quindi ho usato Power Query per rimodellare questa tabella in modo che assomigli alla seguente, utilizzando una semplice trasformazione di colonna non pivot di Power Query.
Una volta creata questa tabella in Power Query (importa dal foglio di calcolo e poi trasforma), Ho chiamato la consulenza “Ho chiamato la consulenza” Ho chiamato la consulenza “Ho chiamato la consulenza”.
Questo lo rende disponibile per l'uso in altre query. senza memorizzare i dati da nessuna parte in Excel. Ogni volta che si "materializza"’ una query aggiungendola come tabella in Excel o PowerPivot, ci vuole spazio. Fallo solo se hai bisogno della vista materializzata della tabella.
Numeri di identificazione della settimana
Avevo bisogno di impostare il budget ogni settimana per la prossima 15 settimane. Stavo usando un calendario settimanale in questo esempio (non un calendario giornaliero), quindi avevo l'ID della settimana come di seguito nel formato YYWW. Si noti il salto non contiguo alla fine dell'anno solare in 2016.
Crea l'elenco degli ID della settimana in Power Query
Questa è stata la mia prima occasione per imparare una nuova abilità... Sapevo che era possibile creare un elenco nativo in Power Query invece di importare l'elenco da Excel (l'ho letto da qualche parte, probabilmente alcuni di Chris Webb) ma non ero abbastanza sicuro di come. Ho cercato su Google e ho provato List.Generate () ma era sbagliato. Dopo, un flashback: tutto quello che dovevi fare era usare la seguente sintassi con parentesi graffe.
= {1..5}
Quanto sopra genera un elenco di numeri dal 1 al 5. Con questo in testa, ho fatto quanto segue:
- Ho creato una nuova query eseguendo i seguenti passaggi del menu:
- Domanda di potenza
- Da altre fonti
- Query vuota
- Ho chiamato la consulenza “Ho chiamato la consulenza”.
- Ho scritto una riga di codice come questa nella barra della formula = {1545..1552,1601..1607} e mi ha dato esattamente quello di cui avevo bisogno. Non sapevo che la sintassi della virgola avrebbe funzionato per creare intervalli non contigui quando ho scritto la formula, ma ha funzionato. Dopo aver fatto pratica ed esperienza con una nuova lingua come questa, inizi a vedere gli schemi nella sintassi e ottieni calci di punizione come questo. (Nota: se non vedi la barra della formula, attivalo dal menu Visualizza)
- Notare che Ho chiamato la consulenza “assolo” una lista, non un tavolo. Per convertire l'elenco in una tabella, Ho fatto clic su Trasforma in tabella, poi ho dato alla nuova tabella un nome di colonna.
- Ho chiamato la consulenza “Ho chiamato la consulenza” Ho chiamato la consulenza, Ho appena creato le istruzioni su come creare la tabella in una nuova query.
A questo punto ero molto felice e impressionato dalle mie nuove capacità., ma c'erano ancora problemi in vista. Il passo successivo è stato l'unione incrociata delle tabelle.
Incrocia le due tabelle
La tabella delle settimane sopra ha 15 righe e la tabella del budget ha 10 righe. Ho chiamato la consulenza “Ho chiamato la consulenza”, quello che voglio dire è che devo duplicare l'intera tabella del budget per ogni settimana, aggiunta della colonna della settimana nella tabella del budget. Quindi ho bisogno di qualcosa come mostrato di seguito, ma per ogni possibile combinazione. vale a dire, 15 X 10 = 150 righe nella mia nuova tabella.
Ho cercato rapidamente su Google Cross Join Power Query e ho trovato alcuni riferimenti a Full Outer Joins, ma questo non è quello di cui avevo bisogno. Poi ho avuto un'idea: forse potresti scrivere una funzione fittizia per simulare il processo di Cross Join. Ho provato questo e ha funzionato bene.
Modificare 31/10/15
Dopo la pubblicazione, Ho ricevuto dei buoni suggerimenti da Imke Feldman; sembra che la funzione non sia affatto necessaria; effettivamente ha senso, ma non avevo capito. Chris Webb ha anche pubblicato un approccio più efficace (che è importante se hai tavole grandi), quindi ecco l'approccio aggiornato.
Aggiungi una colonna personalizzata per creare un cross join
- Apri la tabella delle settimane e aggiungi una nuova colonna personalizzata (mostrato sotto). La formula per la nuova colonna è semplicemente il nome della query di budget.
- Dopo aver fatto clic su OK, Ho chiamato la consulenza “contiene” Ho chiamato la consulenza (come mostrato di seguito).
- Tutto quello che dovevi fare era espandere la nuova colonna facendo clic sul pulsante di espansione (sopra indicato) per creare tutte le possibili combinazioni (mostrato sotto)
Ho configurato i tipi di dati delle colonne numeriche e ho caricato la tabella direttamente nel mio modello di dati in modo da poterla utilizzare in PowerPivot.
Puoi anche leggere il suggerimento di Chris Webb nei commenti qui sotto)
