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
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"

 

  • 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
      Icon for Community Champion rankCommunity 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.