Forum Discussion

kalleleander's avatar
kalleleander
New Member
3 years ago
Solved

Create a table with multiple data in same row

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!

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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.

1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    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.