Forum Discussion

vinayrk's avatar
vinayrk
Regular Visitor
6 years ago
Solved

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...
Need Help
vinayrk's avatar
vinayrk
Regular Visitor
6 years ago

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's avatar
v-xicai
Community Support
6 years ago

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.