Power BI to SQL

Power BI
1

Hello everyone,
I am exploring the possibility of using Power BI to perform SQL operations on a PostgreSQL relational database. Specifically, I am trying to execute insert operations using the PostgreSQL.Database function.

During my tests, I attempted to insert data from an input file containing 10,000 rows. However, I noticed that only one row, sometime 3, is actually inserted into the database, while the others seem to be ignored.

I understand that this approach is unconventional, as Power BI is primarily designed for data analysis and visualization rather than performing database modification operations. Nevertheless, if I can make this setup work, it could open new possibilities for using Power BI within our team.

Has anyone experienced similar situations or could suggest a better way to handle bulk inserts through Power BI? Any suggestions or advice would be greatly appreciated.

Thank you very much!

Roberto
Generated_Table_100000_Rows.xlsx (5.3 MB)
PowerBI2SQL.pbix (50.7 KB)

2

I found the solution. I’ve added a column assigned of the value of the execution of the SQL (SQLOperation([OP])[Status]{0}).
In this way the PostgreSQL.Database (or equivalent for other DB) is called and the DML (in this case an INSERT), is executed.

Regards,
Roberto

let
    Source = Excel.Workbook(File.Contents("C:\Users\rstaltari\OneDrive - DXC Production\Desktop\Generated_Table_100000_Rows.xlsx"), null, true),
    Generated_Table_100000_Rows_Sheet = Source{[Item="Generated_Table_100000_Rows",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Generated_Table_100000_Rows_Sheet, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each true),

    // Corrected SQL query format
    DML = Table.AddColumn(
        #"Filtered Rows",
        "OP",
        each "BEGIN TRY INSERT INTO SalesRecords (RecordID, Name, Client, Amount) VALUES(" & 
            Text.From([RecordID]) & ", '" & 
            Text.From([Name]) & "', '" & 
            Text.From([Client]) & "', " & 
            Text.From([Amount]) & 
            ") END TRY BEGIN CATCH END CATCH; SELECT 'Insert Successful' as Status;"
    ),
   // Forcing the execution of SQL for each row
    Execute = Table.AddColumn(
        DML,
        "SQLOperation",
        each SQLOperation([OP])[Status]{0}, type text
    )
in
    Execute
1 Like