Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have rows that contain arrays of data. I know I can create a drilldown in a Matrix, but that becomes unreadable as there a many different column arrays and the arrays do not have set numbers of items. I only need a rendition of the data in my visual:
Project | Name | Programmers | Start Date | Programs |
Proj1 | Acct | Bob | 5/1/2019 | Enter |
Dave | Modify | |||
Delete | ||||
Proj2 | Inventory | Jill | 10/1/2019 | Enter |
Susan | Modify | |||
Dave | Delete | |||
Even if I separate the arrays into their own table, I still can only show one or I get an error if I try to create a LOOKUPVALUE on the table. Any ideas on how to do this.
Note: these arrays come from [LIST] to [Record].
Thanks,
David
@Anonymous not sure if I understood your question correctly. Can you share sample data and expected output?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Sorry, I'm still fairly new to this. There is a relationship between the tables using Project. I am using Power BI Desktop Aug 2018 due to server constraints. I am attaching my tables and expected output. I know I can expand the [Record] column, but that gives me duplicate records and I would like to avoid that or find a better way. The Programmer column was originally a [Record] in the project table, but I moved it to it's own table. I'm looking for advice and wisdom. 🙂
Thanks,
David
Hi @Anonymous
The least I can do on your scenario is something like this:
This is to avoid the duplicates that you don't want.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzzJU0lFyTE4uAVKm+ob6RgaGlkCma15JapFSrA4+Nb75KZlplQQUuaTmpJakwhUZAYU888pS80ryiyqBbEMDXHbiU4lmMz6lJNgfUJSZV6IUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Name = _t, #"Start Date" = _t, Programs = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Name", type text}, {"Start Date", type date}, {"Programs", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Programs", "Programs - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Programs]), "Programs", "Programs - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"Enter", "Modify", "Delete", "Print"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Programs")
in
#"Merged Columns"
I see what you're doing there, but unfortunately, the data I supplied is only "test" data. In the live data I have multiple fields like "Programs" and "Programmer". And the "Programs" is a free form field. Although I think I could use what you have if you can share the visual you used and it's settings, please.
Thanks,
David
It is a MATRIX VISUAL. You can find it near the table visual. The only setting of he matrix that I changed is under the Row Values and I turned off the Stepped Layout.
Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |