El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Introducció

Power Query és una eina potent dins d'Excel que permet importar, transformar i carregar dades de diverses fonts. En aquest mòdul, explorarem tècniques avançades per maximitzar l'ús de Power Query en la gestió i anàlisi de dades.

Objectius del Mòdul

Aprendre a combinar múltiples fonts de dades.
Utilitzar funcions avançades de transformació de dades.
Automatitzar processos de neteja de dades.
Crear columnes personalitzades amb M Language.
Optimitzar les consultes per a un rendiment millor.

Combinació de Múltiples Fonts de Dades

1.1. Unir Consultes

Unir consultes permet combinar dades de diferents taules basant-se en una columna comuna.

Exemple:

Taula 1: Vendes
+---------+-------+
| Producte| Vendes|
+---------+-------+
| A       | 100   |
| B       | 150   |
+---------+-------+

Taula 2: Preus
+---------+-------+
| Producte| Preu  |
+---------+-------+
| A       | 10    |
| B       | 15    |
+---------+-------+

Pas a Pas:

Aneu a la pestanya Power Query Editor.
Seleccioneu Home > Merge Queries.
Trieu les taules i les columnes per unir.
Seleccioneu el tipus d'unió (Inner, Left Outer, Right Outer, etc.).

1.2. Apilar Consultes

Apilar consultes permet combinar dades de taules amb estructures similars.

Exemple:

Taula 1: Vendes Q1
+---------+-------+
| Producte| Vendes|
+---------+-------+
| A       | 100   |
| B       | 150   |
+---------+-------+

Taula 2: Vendes Q2
+---------+-------+
| Producte| Vendes|
+---------+-------+
| A       | 200   |
| B       | 250   |
+---------+-------+

Pas a Pas:

Aneu a la pestanya Power Query Editor.
Seleccioneu Home > Append Queries.
Trieu les taules a apilar.

Funcions Avançades de Transformació de Dades

2.1. Pivotar i Despivotar Columnes

Pivotar Columnes:

Seleccioneu la columna a pivotar.
Aneu a Transform > Pivot Column.
Trieu la columna de valors i l'agregació.

Despivotar Columnes:

Seleccioneu les columnes a despivotar.
Aneu a Transform > Unpivot Columns.

2.2. Agrupar Dades

Agrupar dades permet resumir informació per categories.

Pas a Pas:

Seleccioneu les columnes a agrupar.
Aneu a Transform > Group By.
Trieu les operacions d'agregació (suma, mitjana, etc.).

Automatització de Processos de Neteja de Dades

3.1. Reemplaçar Valors

Seleccioneu la columna.
Aneu a Transform > Replace Values.
Introduïu els valors a reemplaçar i els nous valors.

3.2. Eliminar Duplicats

Seleccioneu la columna.
Aneu a Home > Remove Duplicates.

Crear Columnes Personalitzades amb M Language

4.1. Introducció a M Language

M Language és el llenguatge de programació utilitzat per Power Query per definir les transformacions de dades.

Exemple:

let
    Source = Excel.CurrentWorkbook(){[Name="Vendes"]}[Content],
    AddColumn = Table.AddColumn(Source, "Preu Total", each [Vendes] * 10)
in
    AddColumn

4.2. Funcions Personalitzades

Exemple:

let
    Source = Excel.CurrentWorkbook(){[Name="Vendes"]}[Content],
    CustomFunction = (vendes as number) as number => vendes * 10,
    AddColumn = Table.AddColumn(Source, "Preu Total", each CustomFunction([Vendes]))
in
    AddColumn

Optimització de Consultes

5.1. Filtrar Dades a l'Origen

Filtrar dades a l'origen redueix la quantitat de dades importades, millorant el rendiment.

Pas a Pas:

Aneu a Home > Advanced Editor.
Afegiu filtres a la consulta M.

Exemple:

let
    Source = Sql.Database("Servidor", "BaseDeDades"),
    Filtrat = Table.SelectRows(Source, each [Data] >= #date(2023, 1, 1))
in
    Filtrat

5.2. Desactivar la Càrrega de Consultes Intermèdies

Aneu a Queries > Query Dependencies.
Desactiveu la càrrega de consultes que no necessiten ser carregades a l'Excel.

Exercicis Pràctics

Exercici 1: Unir Consultes

Creeu dues taules amb dades de vendes i preus.
Utilitzeu Power Query per unir les taules basant-se en la columna Producte.

Exercici 2: Crear una Columna Personalitzada

Importeu una taula de vendes.
Creeu una columna personalitzada que calculi el preu total multiplicant les vendes per un preu fix.

Solucions

Solució Exercici 1:

let
    Vendes = Excel.CurrentWorkbook(){[Name="Vendes"]}[Content],
    Preus = Excel.CurrentWorkbook(){[Name="Preus"]}[Content],
    Unio = Table.NestedJoin(Vendes, {"Producte"}, Preus, {"Producte"}, "Preus", JoinKind.Inner),
    Expandir = Table.ExpandTableColumn(Unio, "Preus", {"Preu"})
in
    Expandir

Solució Exercici 2:

let
    Vendes = Excel.CurrentWorkbook(){[Name="Vendes"]}[Content],
    AddColumn = Table.AddColumn(Vendes, "Preu Total", each [Vendes] * 10)
in
    AddColumn

Conclusió

En aquest mòdul, hem explorat tècniques avançades de Power Query per combinar, transformar i optimitzar dades. Aquestes habilitats us permetran gestionar dades de manera més eficient i automatitzar processos complexos. En el proper mòdul, ens endinsarem en la integració d'Excel amb altres aplicacions i fonts de dades externes.

Tècniques Avançades de Power Query

Introducció

Objectius del Mòdul

Combinació de Múltiples Fonts de Dades

1.1. Unir Consultes

1.2. Apilar Consultes

Funcions Avançades de Transformació de Dades

2.1. Pivotar i Despivotar Columnes

2.2. Agrupar Dades

Automatització de Processos de Neteja de Dades

3.1. Reemplaçar Valors

3.2. Eliminar Duplicats

Crear Columnes Personalitzades amb M Language

4.1. Introducció a M Language

4.2. Funcions Personalitzades

Optimització de Consultes

5.1. Filtrar Dades a l'Origen

5.2. Desactivar la Càrrega de Consultes Intermèdies

Exercicis Pràctics

Exercici 1: Unir Consultes

Exercici 2: Crear una Columna Personalitzada

Solucions

Conclusió

Dominar l'Excel: De Principiant a Avançat

Mòdul 1: Introducció a l'Excel

Mòdul 2: Funcions Bàsiques de l'Excel

Mòdul 3: Habilitats Intermèdies d'Excel

Mòdul 4: Formules i Funcions Avançades

Mòdul 5: Anàlisi i Visualització de Dades

Mòdul 6: Gestió Avançada de Dades

Mòdul 7: Automatització i Macros

Mòdul 8: Col·laboració i Seguretat

Mòdul 9: Integració d'Excel i Eines Avançades