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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
UsePowerBI
Post Prodigy
Post Prodigy

How to add a blank row in a table?

Hello

 

I load a table from a source and I want whenever the table is loaded, an empty row to be added with all the respective fields.

 

What is the best way to do that?

 

Thanks!

1 ACCEPTED SOLUTION

Hi @UsePowerBI ,

 

Create a new table like 

Table = SELECTCOLUMNS(Sheet1,"id",BLANK(),"value",BLANK())

1.PNG2.PNG 

Then union raw table an new table.

Table 2 = UNION('Table',Sheet1)

3.PNG 

Once refresh the table, blank row will be added.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@UsePowerBI You can create a table with a blank Row and append it in either Power Query or DAX (DAX is Union). Power Query would be an Append query. However, if you are thinking that this will separate data refreshes that is not the case. Power BI will reload all of the data and you will get a single blank row at the bottom, for example.

 

What is the purpose of the blank row?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  thanks

 

I created and appended the table below:

Column1

[]

 

The Column1 is the same as the name of a column of the table where I want to add the blank row.

The [] means there is no value.

 

However, it did not work. It did not create a blank row. Please note I do not want to create a blank table with all the column fields because the column fields may change so it will not be sustainable.

 

Is there any other way? How can I do it with DAX?

 

The purpose of the empty row is to add the (blank) entry into slicers so that the blank entries that come from non matches of table joins will be filtered.

 

Thanks!

@UsePowerBI , in Dax you have Row, where you can create a row and then use union and merge it

 

example

Table with Others = UNION(
ROW("ProductCode_wOthers", 0, "Item_wOthers", "0"),
ALLNOBLANKROW('Table','Table'[ProductCode], 'Table'[Item])
)

amitchandak
Super User
Super User

@UsePowerBI , Create a table with the empty row and append with it.

Or you can one table with a column all together different column's name existing, when you will append this will append a new column at end and all other columns will have null values

 

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

@amitchandak  sorry I do not understand what you are saying, it seems there are words missing from your message and I cannot understand it. What should be the format of the table to append please?

@UsePowerBI - Try this:

New Table = 
  UNION(
    'Old Table',
    { ( BLANK(),BLANK(),BLANK(),BLANK(),BLANK(),BLANK() ) }
  )

The number of blanks should be equal to the number of columns that you have. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler @amitchandak 

 

Thanks but that will not work because the number and name of the columns will not stay the same.

 

Is it possible to do it with Power Query?

 

Something along the:

Table.InsertRows(#"Replaced Value", 0,{Column1=""}) 

But it should work regardless the columns.

 

Thanks

Hi @UsePowerBI ,

 

Create a new table like 

Table = SELECTCOLUMNS(Sheet1,"id",BLANK(),"value",BLANK())

1.PNG2.PNG 

Then union raw table an new table.

Table 2 = UNION('Table',Sheet1)

3.PNG 

Once refresh the table, blank row will be added.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors