Forum Discussion
Building a Report with Multiple Lists per Cell in Each Row
Hello all,
I am working with a fairly small list of vendors (about 100) with 3 additional columns containing normalized attributes like vendor type, areas of focus, and operating states. Unfortunately, the cells in each of those 3 columns contain lists of data which makes filtering and reviewing quite difficult to our normal users. For example, Vendor A may have 2 vendor types, 5 areas of focus, and could operate in any number of US states.
Is it possible for PowerBI or PowerQuery to recognize lists within each cell or do I need to create a master table with each possible unique combination for each vendor (which would obviously increase the size of my data table by many orders of magnitude)?
Thank you in advance!
Hi chrismadson
You can put the following code in 'Advanced Editor' in Power Query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc+xDsIgEAbgVzHM/1Ja3QkVraHXQaRWwuDQwcHEdPPtvdJoxLhcgOP/DkIQ7iwgtIKx2Ck0FqRBA44aXqH33HTPx7gqeKGm8VpgrjLVks/MbbpzMyIIauc7F+gOTY3WgTxODj0zwy+z/sfIxBjLewaYoc94mY2v8lyZcukj9RZ0QLf/fjkyYbNkF+ctVCLGFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PRACTICE STATES(S)" = _t, #"ADDITIONAL PRACTICE STATE(S)" = _t, #"TYPE1/TYPE2" = _t, #"PRACTICE AREAS/LOB" = _t, FIRM = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRACTICE STATES(S)", type text}, {"ADDITIONAL PRACTICE STATE(S)", type text}, {"TYPE1/TYPE2", type text}, {"PRACTICE AREAS/LOB", type text}, {"FIRM", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"ADDITIONAL PRACTICE STATE(S)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ADDITIONAL PRACTICE STATE(S)"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ADDITIONAL PRACTICE STATE(S)", type text}}), #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"TYPE1/TYPE2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TYPE1/TYPE2"), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"TYPE1/TYPE2", type text}}), #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"PRACTICE AREAS/LOB", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "PRACTICE AREAS/LOB"), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"PRACTICE AREAS/LOB", type text}}) in #"Changed Type3"Is this the output you want?
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi chrismadson
Maybe you can use merge function in power query, please refer to the following link:
Merge queries overview - Power Query | Microsoft Learn
If this cannot meet your question, can you provide some sample data or the picture that you expect?
Best Regards,
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the reply! Unfortunately the merge function isn't what I am looking for.
Below is an example of the data that I have in its current format. If you notice, columns 2, 3, and 4 contain lists of attributes by which I need to filter. The problem is, each list is its own unique selection in a filter so I can't simply choose to see all who practice in WV who are Type 2 and Specialize in Area 3, for example.
I am trying to avoid having to create dozens (if not over a hundred) rows for each firm to create a unique entry for every possible combination of values. The ultimate goal is a map visualization where I can click on a state, select the Type and Area from a drop down, and get a listing of all possible firms with those critera.
Hi chrismadson
You can put the following code in 'Advanced Editor' in Power Query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc+xDsIgEAbgVzHM/1Ja3QkVraHXQaRWwuDQwcHEdPPtvdJoxLhcgOP/DkIQ7iwgtIKx2Ck0FqRBA44aXqH33HTPx7gqeKGm8VpgrjLVks/MbbpzMyIIauc7F+gOTY3WgTxODj0zwy+z/sfIxBjLewaYoc94mY2v8lyZcukj9RZ0QLf/fjkyYbNkF+ctVCLGFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PRACTICE STATES(S)" = _t, #"ADDITIONAL PRACTICE STATE(S)" = _t, #"TYPE1/TYPE2" = _t, #"PRACTICE AREAS/LOB" = _t, FIRM = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRACTICE STATES(S)", type text}, {"ADDITIONAL PRACTICE STATE(S)", type text}, {"TYPE1/TYPE2", type text}, {"PRACTICE AREAS/LOB", type text}, {"FIRM", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"ADDITIONAL PRACTICE STATE(S)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ADDITIONAL PRACTICE STATE(S)"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ADDITIONAL PRACTICE STATE(S)", type text}}), #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"TYPE1/TYPE2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TYPE1/TYPE2"), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"TYPE1/TYPE2", type text}}), #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"PRACTICE AREAS/LOB", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "PRACTICE AREAS/LOB"), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"PRACTICE AREAS/LOB", type text}}) in #"Changed Type3"Is this the output you want?
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
