Dynamically Expand Sharepoint List in Multiple Columns

Power Query
1

I have multiple columns with SharePoint lists and want to dynamically expand the list. Unfortunately these are sensitive data so I am unable to share but I can explain.

My data has 2 major columns with list that are lookup fields selected per the complaint bases and issues submitted. For example the bases column has the following lookup fields; Sex, Race, Disability etc. These constitutes the list for each of the complaint.

I want to expand the list dynamically so I can visualize the bases and issues column.
In attempting the solution I added a new custom column by using the following M-query and expanded

" Table.FromColumns({[Bases], [Issues]})"

but got errors (We cannot convert the value null to type list).

So I replaced the nulls with blanks but still got the errors.

Any help will be highly appreciated

2

Welcome to the forum David!
I assume null was just the value in the first row of [Bases]:
Try Table.FromColumns({{[Bases]},{[Issues]}})

Regards,
Matthias

3

Thanks for taking the trouble to take on this am humbled. While this solves the error problem it does not expand the 2 columns but rather combines the 2 columns into one. One pair of curly bracket gives the 2 columns you can expand to, however 2 pair curIy bracket gives one combined column. I want to maintain while expanding the 2 columns all at once, instead of expanding the column one at a time.

4

David, you described the error you have and getting rid of the error doesn’t make you happy. :unamused:

No need to disclose your sensitive data, but a small dummy data set and/or mock up screenshots illustrating what you have and what you want to achieve might be helpful.

5

Mock up screenshot.docx (172.3 KB)
Have attached a mock up screenshot. the highlighted columns without errors for all rows and that is expandable to 2 columns is what is desired.

6

David, do you have rows which have no List but show null?
=> If so replace these nulls with an empty list {}.

If you have nulls within the list that should not cause any issue. You can see that they combine and expand just fine:

image
image832Ă—456 16.5 KB

image
image1023Ă—226 13.6 KB 

    #"Added Custom" = Table.AddColumn(prevStep, "Custom", each Table.FromColumns({[Bases],[Issues]})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"BasesData", "IssuesData"})
7

Expand Column error.docx (184.5 KB)
I replaced the nulls with list {} but still error persist. Please see attached screen shot and let me know if I miss anything in your instructions. Thank you

8

Hi @damoako,

You’ve entered:

"{}"

That returns a text string, omit the double quotes so the syntax returns an empty list.
@Matthias great support!

9

David, you need to replace it with an empty List:
= Table.ReplaceValue(prevStep,null,{},Replacer.ReplaceValue,{“Bases”, “Issues”})

    #"Replaced Value" = Table.ReplaceValue(prevStep,null,{},Replacer.ReplaceValue,{"Bases", "Issues"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Table.FromColumns({[Bases],[Issues]})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"BasesData", "IssuesData"})
10

@Melissa I used yesterday’s challenge data to get the lists for my screenshots. :blush:

2 Likes
11

Thank you I did not enter with a quote when I replaced the null value with {} . It was entered automatically by power query. Had to examine the output code in the editor and observe the issue you raised. Upon removal in the editor, issue was fixed

1 Like
12

@Mathias thank you for your patient and commitment. Issue is now resolved. Am new here is there a resource that goes more indept on how to prepare sharepoint list data for visualization. I will like to explore further into how others have approached similar situation.