Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @UsePowerBI ,
Create a new table like
Table = SELECTCOLUMNS(Sheet1,"id",BLANK(),"value",BLANK())
Then union raw table an new table.
Table 2 = UNION('Table',Sheet1)
Once refresh the table, blank row will be added.
Best Regards,
Jay
@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?
@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])
)
@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.
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())
Then union raw table an new table.
Table 2 = UNION('Table',Sheet1)
Once refresh the table, blank row will be added.
Best Regards,
Jay
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |