Create a table with multiple data in same row

[kalleleander]

Hi, I am trying to make a table similar to an Excel table in Power BI.

An example query looks like this:

And the table that I have made looks like this:

I wish for a table where there are no spaces, so multiple data can be on the same row, as if you were to fill out an Excel sheet. So the list of e.g. projects in phase 1 would just start from the top, and the same with the other phases.

Hope you can help!

[ImkeF · Accepted solution]

Hi @kalleleander ,
unfortunately you have to use some M-code here. Please paste the following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WMlTSUVKK1YlWAtLGYIYRkogJjAGjTZViYwE=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t, Column2 = _t]
    ), 
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", Int64.Type}, {"Column2", Int64.Type}}), 
    Custom1 = List.Transform(
        Table.ToColumns(#"Changed Type"), 
        each List.Select(_, (l) => l <> null and l <> "")
    ), 
    Custom2 = Table.FromColumns(Custom1)
in
    Custom2

In there, you split up the table into a list of single columns (which will then become list objects). 
Next, you filter those list objects so no empty fields will be in there any more.
Then you re-assemble to a table again.