Forum Discussion
Mange Data in Excel with Power Query
123abc Thank you so much for your kind words, I'm happy I was able to help you! Please accept the message above with the code as the solution, to assist other users as well!
Regarding point 5, here's the explanation.I've used a M code that filters the table `"Added Index"` using `Table.SelectRows`, keeping only rows where:
1. `Column1` contains "Tel:", "Email:", or "Website:".
2. `Column1` is fully uppercase and not empty (`Text.Upper([Column1]) = [Column1] and [Column1] <> ""`).
3. If none of the above applies, the row is ignored (`null`).BBF
123abc ok, starting by your Excel, you can use this Power Query code:
let
// Importa il file Excel
Source = Excel.Workbook(File.Contents("C:\Users\bfumagalli\Downloads\Gulfood page 2-20.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
// Aggiungi una colonna indicizzata per identificare i gruppi
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
// Filtra solo le righe che contengono i dati di interesse
#"Filtered Rows" = Table.SelectRows(#"Added Index", each
Text.Contains([Column1], "Tel:") or
Text.Contains([Column1], "Email:") or
Text.Contains([Column1], "Website:") or
(Text.Upper([Column1]) = [Column1] and [Column1] <> "") or
null),
// Aggiungi una colonna personalizzata per identificare i nomi delle aziende
#"Added Company Column" = Table.AddColumn(#"Filtered Rows", "Company Name", each if not Text.Contains([Column1], ":") then [Column1] else null),
// Propaga il nome dell'azienda verso il basso
#"Filled Down" = Table.FillDown(#"Added Company Column", {"Company Name"}),
// Filtra le righe con le informazioni effettive (Tel, Email, Website)
#"Filtered Info Rows" = Table.SelectRows(#"Filled Down", each Text.Contains([Column1], "Tel:") or Text.Contains([Column1], "Email:") or Text.Contains([Column1], "Website:")),
// Estrai il tipo di informazione (Tel, Email, Website)
#"Added Info Type" = Table.AddColumn(#"Filtered Info Rows", "Info Type", each
if Text.Contains([Column1], "Tel:") then "Tel"
else if Text.Contains([Column1], "Email:") then "Email"
else if Text.Contains([Column1], "Website:") then "Website"
else null),
// Rimuovi le informazioni superflue
#"Cleaned Info" = Table.TransformColumns(#"Added Info Type", {{"Column1", each Text.AfterDelimiter(_, ": "), type text}}),
// Trasforma la tabella in formato largo
#"Pivoted Info" = Table.Pivot(#"Cleaned Info", List.Distinct(#"Cleaned Info"[Info Type]), "Info Type", "Column1"),
// Riordina le colonne per chiarezza
#"Reordered Columns" = Table.SelectColumns(#"Pivoted Info", {"Company Name", "Email", "Tel", "Website"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Company Name"}, {{"Tel", each List.Max([Tel]), type nullable text}, {"Email", each List.Max([Email]), type nullable text}, {"Website", each List.Max([Website]), type nullable text}})
in
#"Grouped Rows"
You'll obtain:
Let me know if it's ok, and in this case please accept my answer as solution!
BBF
Hi, BBF
First of all thank you so much for you support and help, can you pleae provide me this sample file of excel beacuse i try this query but cant run, i cant undersatnd why this is happen.
Regards:
Ali Abbas
I am using this query in Add Column + Custom Column + Custom column formula.
But cant work.
123abc yes, send me your email, here or ad pvt message and i'll send to you the pbix. The Excel i used the one you've shared with me.
BBF