Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  looking for a way to use an excel file and using the column as parameters to sql query, insert data into the excel file

    Bronze Contributor
    Posted Jan 24, 2022 02:33 PM
    Hi

    looking for a way to use an excel file and using the column as parameters to sql query, insert data into the excel file
    Say I have

    Last Name, First Name and DateOfBirth columns in an excel file

    I import that into Power BI

    How can I use each row's Lname, Fname and DOB to query an sql table and pull back values to fill in the City, state and zip columns in the excel spreadsheet?

    Thanks
    Dean-O

    ------------------------------
    Dean-O Rochester
    Programmer Developer
    ------------------------------


  • 2.  RE: looking for a way to use an excel file and using the column as parameters to sql query, insert data into the excel file

    Posted Jan 25, 2022 02:56 AM
    I worked on that before. First of all you need to refresh dataset to get the data from excel file because you've imported it. And limitation is that there is count of refresh limit remotely. I say remotely cause i've added a power flow button in powerBI to refresh dataset and it has 8 resresh limits per day. But in Premium license the rules will be change.
    And if you exit the report and refresh dateset manually then you no limit to refresh.

    So i prefer not to use this option. But you may try the visual "Acterys Table Edit"

    ------------------------------
    Kadir Başeğmez
    ------------------------------



  • 3.  RE: looking for a way to use an excel file and using the column as parameters to sql query, insert data into the excel file

    Top Contributor
    Posted Jan 25, 2022 08:53 AM
    Edited by Sam Duval Jan 25, 2022 08:56 AM
    While it's possible, it might be more of a headache than its worth. You'd probably be better off importing the excel sheet and your SQL connection into power bi. and making a composite key column out of the three fields you mention either in excel or in the power bi model (create a DAX/Power Query column) and doing the same on SQL side. This will let you then use power bi's single key joining logic.

    If you must do it in excel there are at least two options I can think of off the top of my head:
    create a new query for each row, then in the advanced query editor, you have to write a line for each variable you want to extract from excel it will have to be formatted as a table, and not sure how you'd go about getting a specific row. other than a macro to loop through the table row by row and run a query refresh, using the macro to write the current row values to another table in the spreadsheet. Or in power automate when dealing with an array (table) you can put outputs()[x] where x is the line/row number you want to extract, not sure if that logic works in excel.

    Here is a query I wrote a few years back using information from other cells in excel basically a "table" of values I was wanting to query for. It's been a few years so the code could probably be greatly improved upon, but it works.
    As_Of_Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"As_Of_Changed_Type" = Table.TransformColumnTypes(As_Of_Source,{{"As_Of", type date}}),
    As_Of = #"As_Of_Changed_Type"{0}[As_Of],
    Minors_Source = Excel.CurrentWorkbook(){[Name="Unused_Lines_Of_Credit"]}[Content],
    Minor_Code_Grouping = Table.Group(Minors_Source, {"GroupOn"}, {{"MinorFilter", each Text.Combine([Minor_Codes], ", "), type text}}),
    Minors_Group_On = Table.RemoveColumns(Minor_Code_Grouping,{"GroupOn"}),
    Minors_List = Table.ToList(Minors_Group_On,Combiner.CombineTextByDelimiter(",")),
    Minors_Delimited = Text.Remove(Text.Combine(Minors_List,"'"),""""),
    Effective_Date = Number.ToText(Date.Year(As_Of)) & Text.End("0" & Number.ToText(Date.Month(As_Of)),2) & Text.End("0" & Number.ToText(Date.Day(As_Of)),2),
    QrySrc = "exec Finance_Accounting.Concentration_Risk_Reports '" & Effective_Date & "'",
    SERVER = "5555555555555555555",
    DATABASE = "5555555555555555",
    Source = Sql.Database(SERVER, DATABASE, [Query=QrySrc]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EOM_PROD_DT", type date}, {"CONTRACT_DATE", type date}, {"Last_Payment_Date", type date}, {"MATURITY_DATE", type date}})
    in
    #"Changed Type"
    ​

    The other option I've not deployed/tested, but I think there is a way that you can take the results of a query or excel table if the excel file is static, and import that into the data model, then within power query editor, you might be able to reference that table/list and join it to your SQL results and then push the combined results back out

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 4.  RE: looking for a way to use an excel file and using the column as parameters to sql query, insert data into the excel file

    Posted Jan 26, 2022 04:12 PM

    Hi Dean,

    I know not much about SQL, however I know a lots about excel and Power BI. And every time I can see columns 'First Name', 'Last Name', 'DOB' I am scared. I spend a looooot of time to translate all 'Tom', 'Tomas', 'Tommy', 'Tomms' into simple 'Thomas'.

    Or that Thomas Johnes DOB 26/01/1990 from London Is not same as Thomas Johnes DOB 26/01/1990 from Manchester

    In all my tables I ALWAYS use Identifier for relation, NEVER names. At least I can trust ID are not duplicated.

    Unless your database is 20 people and you know all of them and you have no 'name twilings'.



    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 5.  RE: looking for a way to use an excel file and using the column as parameters to sql query, insert data into the excel file

    Posted Jan 27, 2022 01:28 PM
    Not really sure what you are trying to do with Power BI but it is easy to put parameters in Excel, Get data from SQL using Power Query with the parameters, and then put the results back into an Excel sheet.

    I create a parameters sheet that has the parameters in a table:
    ParameterName        ParameterValue
    ProjectNumber ABCDE

    Then use the parameters in a query in Power Query:

    Parameter = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ProjectNumber_Value = Parameter{0}[ParameterValue],
    Source = Sql.Database("ServerName", "DatabaseName", [Query="SELECT field1, field2, field3 FROM MyTable WHERE field1 = '" & ProjectNumber_Value & "'"])

    For the parameters I usually use a pick box, often that is populated with data using a query from the database so that users don't type mistakes. Then the parameter field in the parameter table refers to the value in the pick box.

    ------------------------------
    John Twohig
    ------------------------------