cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User IV
Super User IV

@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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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])
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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.

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors