Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kalleleander
New Member

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:

kalleleander_0-1666099885644.png

 

And the table that I have made looks like this:

kalleleander_1-1666099914025.png

 

 

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!

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.