Forum Discussion
Unable to create SQL DB view
Hi ,
I have an sample table which needs to have the following modifications.
1) Replace -1 to Null on an column priority column
2) Change data format to date on Datacreated column
While I was able to create these 2. I'm unable to create the view with Alias prepended into the columns. Screenshots attached.
Any Ideas on how to create an view on the DB end to prepend the alias name to each column.
Original Table :
PowerBI Transformation
Need to recreate the powerbi transformation on the DB end.
Hi vinayrk ,
Assuming the table name is called "a", then you may type codes like below in the SQL DB.
SELECT a.* FROM … Or SELECT a.assetId as "a.assetId", a.organizationId as "a.organizationId", a.dateCreated as "a.dateCreated", a.dateModified as "a.dateModified", a.installDate as "a.installDate", a.manufacturer as "a.manufacturer" FROM TableA a JOIN TableB b ON a. manufacturer = b.manufacturerFor reference:
In a join, how to prefix all column names with the table it came from
SQL select join: is it possible to prefix all columns as 'prefix.*'?
SQL: How do add a prefix to column names in a JOIN?
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
vinayrk , Not very clear
In power BI, you can right click and use replace null with any value. https://yodalearning.com/tutorials/learn-how-replace-values-power-query/
Do you need the view in DB
create view as
select View1, coalesce(Col1,'NA') from table
You can add query of choice in advance option in connection
Hi Amit,
I'm clear on how to replace values and change data part. I'm making those changes on the DB side.
What im not able to create is an view of the original table with the alias name prepended into each column.
I want to recreate the table structure that was in powerBi on the DB end. This is how it looks in the current PBI report.
// Expand the table to multiple columns, and prepend a table alias to each field name.
TableData = Table.ExpandRecordColumn(SingleColumnTable, "Column1", {
"assetId",
"organizationId",
"dateCreated",
"dateModified",
"installDate",
"manufacturer",
},
{
"a.assetId",
"a.organizationId",
"a.dateCreated",
"a.dateModified",
"a.installDate",
"a.manufacturer",
})We have been asked to make this calculation run on the DB end rather than powerbi. Our view on the DB end is missing a."column name" which we are unable to recreate
Hi vinayrk ,
Assuming the table name is called "a", then you may type codes like below in the SQL DB.
SELECT a.* FROM … Or SELECT a.assetId as "a.assetId", a.organizationId as "a.organizationId", a.dateCreated as "a.dateCreated", a.dateModified as "a.dateModified", a.installDate as "a.installDate", a.manufacturer as "a.manufacturer" FROM TableA a JOIN TableB b ON a. manufacturer = b.manufacturerFor reference:
In a join, how to prefix all column names with the table it came from
SQL select join: is it possible to prefix all columns as 'prefix.*'?
SQL: How do add a prefix to column names in a JOIN?
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
