Unable to create SQL DB view

[vinayrk]

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.

[amitchandak]

@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

[vinayrk]

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

[v-xicai · Accepted solution]

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

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