0

Hello hope someone can assist me in a power query I'm having trouble with. I'm brand new to Power Query and the M language and while I do have some coding background coding is not my day job. I'm pulling data from web page and the data in one column that is a list nested in a list.
This is a clip of what I see at from query initially:

enter image description here

I then drill down on the list and see this for all of the rows:

enter image description here

I then drill down again in that list and get a this:

enter image description here

At this level I get at least one row but there could be many rows.

What I want is to take all of the values and combine them into one cell as a bulleted list like this:

enter image description here

Any assistance on how to do this would be appreciated

I've tried looking at some of the examples in other threads and only get errors when I do this.

1 Answer 1

1

You didn't really provide enough detail here, but it looks like a bunch of lists within lists

you can run them through something like this to expand them all. If the results dont look like what you want, provide more information and sample data we can reproduce

let  Source = <<copy whatever your source is here>>,
//Marcel Beug 2017
TableSchema = Table.Schema(Source),
ColumnNames = Table.SelectColumns(TableSchema,{"Name"}),
IsListColumn = Table.AddColumn(ColumnNames, "IsListColumn?", each List.AllTrue(List.Transform(Table.Column(Source,[Name]), each _ is list))),
NonListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = false)),
NonListColumnNames = Table.RemoveColumns(NonListColumns,{"IsListColumn?"})[Name],
SelectNonListColumns = Table.SelectColumns(Source,NonListColumnNames),
ListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = true)),
ListColumnNames = Table.RemoveColumns(ListColumns,{"IsListColumn?"})[Name],
SelectListColumns = Table.SelectColumns(Source,ListColumnNames),
TableFromLists = Table.AddColumn(SelectListColumns, "TableFromLists", each Table.FromColumns(Record.FieldValues(_))),
ListTables = Table.SelectColumns(TableFromLists,{"TableFromLists"}),
Custom1 = Table.FromColumns({Table.ToRecords(SelectNonListColumns),Table.ToRecords(ListTables)}),
#"Expanded Column1" = Table.ExpandRecordColumn(Custom1, "Column1", Table.ColumnNames(#table(List.Min({1,List.Count(NonListColumnNames)}),{})), NonListColumnNames),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"TableFromLists"}, {"TableFromLists"}),
#"Expanded TableFromLists" = Table.ExpandTableColumn(#"Expanded Column2", "TableFromLists", Table.ColumnNames(#table(List.Count(ListColumnNames),{})), ListColumnNames),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded TableFromLists",ColumnNames[Name])
in #"Reordered Columns"

EDIT for specific website clarification

let  Source = Web.Page(Web.Contents("https://ised-isde.canada.ca/site/high-speed-internet-canada/en/universal-broadband-fund/selected-universal-broadband-fund-projects")),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Location of project", "Number of Households to be served / Number of kilometers to be covered (mobile projects)", "Funding recipient", "Funding amountFootnote *"}, {"Location of project", "Number of Households to be served / Number of kilometers to be covered (mobile p", "Funding recipient", "Funding amountFootnote *"}),
#"Added Custom" = Table.AddColumn(#"Expanded Data", "Location of Project2", each Text.Combine([Location of project]{1},"#(lf)")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Source", "ClassName", "Id", "Location of project"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Caption] <> "Document")),
#"FundingToAmount" = Table.TransformColumns(#"Filtered Rows",{{"Funding amountFootnote *", each Number.From(Text.Select(_,{"0".."9",".","$"})), type number}})
in  #"FundingToAmount"

then in excel format that column as text control [x] wrap text

Sign up to request clarification or add additional context in comments.

2 Comments

You are correct that that I have list within a list. I'm trying to take the text at the end and put in a new custom column. The data is on a public webpage at this url: ised-isde.canada.ca/site/high-speed-internet-canada/en/… Sorry if this is still not clear
Edited my answer at bottom. That would have been helpful it that was your initial question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.