How to populate query [List]

**Robork** · 08-08-2023, 04:19 PM

I am new to queries in Excel and can't seem to find another post with my issue, though I suspect I am not using the right terms.

I have queried a data table, and certain columns in the query contain a list of 1-4 different classifiers. When editing my query I can preview these lists, but in my Excel book I only have the text "[List]" for these entries.Screenshot 2023-08-08 155505.png Screenshot 2023-08-08 161805.png
Is there a way to have those lists populate, either as separate entries, or concatenated in the same cell?

Thanks in advance for any guidance you can share!

**AliGW** · 08-09-2023, 12:32 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**Robork** · 08-09-2023, 09:29 AM

Hello AliGW,

Thank you for your response and welcoming me!

My issue with attaching a sample workbook with 10-20 entries is that my query has 900 someodd entries, and I dont know how to limit entries in that way.

The data is publicly available online, so I am not sure what needs to be desensitized.

I would just like to pull this data table directly from the URL in the format it appears there (listed entries appear as wrapped text in cells) rather than a[List] entry that I cannot even expand.

I am happy to comply with any forum rules in order to get guidance on my issue, but I may need some guidance to be able to comply properly. I appreciate your patience with my lack of experience.

Thanks,
Rob

**AliGW** · 08-09-2023, 09:32 AM

If the data isn't sensitive, there is no need to desensitise it, but only you know this!

Let's try another tack: wheere is the data to be found online: Post the URL wit TWO spaces added to it.

How EXACTLY have you brought that data into Excel to arrive at the stage you are at in the screenshot (clear steps, please).

We need something to work with, otherwise it's all just guesswork.

**Robork** · 08-09-2023, 10:19 AM

The data is from the Canadian governments' public list of all cannabis licenses:
canada.ca/en/health-canada/services/drugs-medication/cannabis/industry-licensees-applicants/licensed-cultivators-processors-sellers.html

In order to pull the data as in my screenshot, I used the 'Get & Transform data - From Web' tool in office 365. I added the URL and followed through prompts without making any modifications.
Screenshot 2023-08-09 101151.png

Screenshot 2023-08-09 101217.png

Screenshot 2023-08-09 101301.png

I did try to make some modifications in a copied document to experiment, but I feel like I am going down the wrong path. I had landed on a support page with all the List. functions for modifying power queries.
Screenshot 2023-08-09 100729.png

Screenshot 2023-08-09 100447.png
It may be that I did not understand the proper syntax to implement these functions in the query.

**romperstomper** · 08-11-2023, 06:51 AM

Something like this should work as long as there aren't any nested lists in there:

let
    Source = Web.Page(Web.Contents("canada.ca/en/health-canada/services/drugs-medication/cannabis/industry-licensees-applicants/licensed-cultivators-processors-sellers.html")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Licence holder", type text}, {"Province / territory", type text}, {"Licence(s)", type any}, {"Classes of cannabis the licence holder is authorized to sell to provincially/ territorially authorized distributors/retailers", type any}, {"Classes of cannabis the licence holder is authorized to sell to registered patients", type any}, {"Client Care phone numbers for registered patients", type text}, {"Date of initial licensing (year-month-day)", type text}}),
    Result = Table.TransformColumns(#"Changed Type", {}, each try Text.Combine(_, ", ") otherwise _)
in
    Result

**Robork** · 08-14-2023, 10:15 AM

Thank you so much! This worked for me!

How to populate query [List]

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to populate query [List]

Re: How to populate query [List]

Re: How to populate query [List]

Re: How to populate query [List]

Re: How to populate query [List]

Re: How to populate query [List]

Re: How to populate query [List]

Thread Information

Users Browsing this Thread

Similar Threads

Power Query , extract LIST and RECORDS from same query

Can I populate a ComboBox from a SQL query?

Populate A Form Combobox With A SQL Query?

[SOLVED] Auto Populate Order Form, based on qty's filled in Price list - Query

Data validation list, how to populate/prefill value in the list? (first in the list?)

[SOLVED] Loop through list, perform web query and save each query on its own page

Populate worksheet by query

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions