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 items.  I only need a rendition of the data in my visual:

ProjectNameProgrammersStart DatePrograms
Proj1AcctBob5/1/2019Enter
  Dave Modify
    Delete
Proj2InventoryJill10/1/2019Enter
  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

 

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        mussaenda
        Icon for Community Champion rankCommunity 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"