Mange Data in Excel with Power Query

[123abc]

Hi Experts:

I have a Gulfoods Catalogue  i wants to extrract the following Informaiton from it and wants to organizae it in excel 4 columns parallely like:

1.Comany Name

2.Tel

3.Email

4.Website

Please guide me how to do this with power bi power query or with excel power query.

I have this catlog in pdf and also extract with excel with online sourece. please guide how to extract and organize to minimizse mannual efforts.

Thank.

[BeaBF]

@123abc can  you share the excel structure? so that we can start from it.

BBF

[123abc]

Hi BeaBF,

It is requested to you plase follow the link:

https://docs.google.com/spreadsheets/d/1mPCUvnwmiIa2bnDtAtkDh_a_HzOn1IIJ/edit?usp=sharing&ouid=10677...

i have saved the file in above mentiond one drive link.

[BeaBF]

@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

[123abc]

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

[123abc]

I am using this query in Add Column + Custom Column + Custom column formula. 

But cant work.

[BeaBF]

@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

[123abc]

My E-Mail ID.

aliabbasrajput@yahoo.com

Thank You So Much.

[BeaBF]

@123abc Hi! have you seen the pbix? it's ok?

BBF

[BeaBF]

@123abc Sent! Then if this solution it's ok, please accept my previous message as solution.

BBF

[123abc]

Thank You Very Much .. Please let me check.

[123abc]

I sincerely appreciate the time and effort you took to solve my Power BI issue related to Power Query and data extraction. Your solution was clear, precise, and incredibly helpful in resolving my challenge. It's always inspiring to see such willingness to share knowledge and expertise. Thank you for your valuable support and guidance. I hope to learn more from you in the future!

I am just stcuk to understand step 5......😣

[BeaBF · Accepted solution]

@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]

Thank You so much this has resolved now. I am bit confused on a little applied step # 5 Filtered Rows, after step # 4 in applied steps Added index, how you added this step # 5, I mean are you using add Column + Custom Column and adding Table.SelecRow(.....) M Function or using any other approach bcz i just stuck here ...! and onward, all steps i have understad wtih the help and guidence of you kind support.  Thank You Very Much again for your kind support.

Regards 

Ali Abbas