Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Showing multiple column values on one row

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 it...
Need Help
mussaenda's avatar
mussaenda
Community Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    mussaenda
    Community Champion
    6 years ago

    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.