Forum Discussion

123abc's avatar
123abc
Community Champion
1 year ago
Solved

Mange Data in Excel with Power Query

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.

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • BeaBF's avatar
    BeaBF
    1 year ago

    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

13 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

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

     

    BBF

      • BeaBF's avatar
        BeaBF
        Super User
        1 year ago

        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