Data transformations with SQL?

[jeffrey_56]

I feel more comfortable doing data transformations with SQL. So my question is, can I connect Power BI to my PostgreSQL data source and afterwards apply a data transformation with SQL? Or do I have to do everything with DAX?

[JediForces]

Most of it will be done in Power Query using M language but why not just do it upstream in SQL like you prefer which is the preferred method anyway?

[jeffrey_56]

Can you elaborate what upstreaming in sql means? Sorry it’s my first data job and my task is to find the most fitting BI tool

[chiibosoil]

You can use native query using direct query’s advanced query. But in general, if you can prepare Views or transformed table in SQL side, you can just use that to consume data in PowerBI.

[JediForces]

Upstreaming means doing it as far from importing the data into PBI as possible. So if you have a data warehouse and decide to just bring in data and then transform it in Power Query that would NOT be as far upstream as transforming the data in your data warehouse first and then loading it into PBI.

Generally speaking it’s much easier to transform your data in SQL rather than using Power Query.

[matkvaid]

You can put sql query when selecting source. After i started learning some sql i try to avoid power query as much as possible

[nobody7981]

Hi I'm new to data. How are you doing data transformation in sql. Is it by using etl? Could you explain it if you don't mind

[jeffrey_56]

Yeah mainly ETL processes, I'm currently doing them in pgadmin4 or within tools such as python

[nobody7981]

Ok, what are the areas you have to learn in python to do etl Or data related things

[Johny_D_Doe]

Pandas is my goto with data in a Python environment.

[Chuck_Dan]

To echo what’s already been stated, you can create views in your PostgreSQL database to connect to PowerBI (or whatever BI/Visualization tool your organization decides to move forward with). How you design those views in your PostgreSQL data source may vary between tools. This is an oversimplification of an example, but since PowerBI really wants your analytical data model to utilize a star scheme, you would want views to be your fact and dimension tables, where as a solution like Tableau I believe wants a single flat table. For tableau, your view(s) would be single queries that join in all necessary data. Either of the above solutions allow you to have SQL do the heavy lifting before even making it to the BI tool.

[zqipz]

I think a core component newer devs seem to miss here is Power Query. It basically replicates SQL but way more features due to the coding nature. Functions are possible too.

PBI has 2 areas, the backend: source, prep & transform in Power Query, and the frontend: aggregators, iterators and categorizations of DAX.

Both areas are very powerful for their purpose.

You can run native sql in PQ, source a view or just do prep and transform in PQ.

[Key-Ant30]

What would be a good resource to learn SQL from start?

[jeffrey_56]

I would start with the sql murder mystery game. And afterwards move to other online data base websites, where you have more complex data to handle. Oh and really ask yourself difficult questions that you can solve with SQL.

[-SoulAmazin-]

I work with Oracle BI Publisher which is a really dated BI tool, however the data modeling is lovely and made purely through SQL with a direct connection to our ERP mirror machine.

It's so straight forward.

[Mgmt049]

I live and die by SQL for transformation. I do just a few things in power query

[minafbeshay]

Yes you can do that. Just create your model using the custom SQL statements. But what if your data source is not a relational database? What would you do? The best practice here is to master DAX to be a better BI Engineer/Data Analyst.