Showing multiple column values on one row

[Anonymous]

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
				Print

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

[parry2k]

@Anonymous not sure if I understood your question correctly. Can you share sample data and expected output?

[Anonymous]

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

[mussaenda]

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"

[Anonymous]

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

[mussaenda]

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.