Search code examples
pythonpowerbissaspowerqueryclipboard

Paste multiple queries from text file into Power BI Desktop

I am porting a SSAS (SQL Server Analysis Server) Tabular model to Power BI Desktop. I have about 200 queries in the SSAS export (just in a text file) that I need to move into Power BI. I can paste the queries one at a time into the Advanced Editor in Power BI, but for 200 queries, this is a slog. I am looking for a way to import all the queries at once into Power BI Desktop.

For SSAS experts, this is attempt 4 to migrate the tables from SSAS. Attempt 1 was to use ALM Toolkit, but it does not work with tables in Power BI Desktop models. Attempt 2 was an XLMA script in SSMS, but I can't even get the script that is generated for the SSAS model to run in SSAS. I get errors like: Query (56,129) The syntax for ')' is incorrect. Attempt 3 in Tabular Editor had the same issue as SSMS.

So for attempt 4, I thought I'd try to paste multiple queries into the Power Query window. If I move queries from one Power BI file to another, I can select multiple queries and copy and paste them between Power BI files. If I look at the clipboard, it looks like this:

Format:HTML Format Version: 1.0 StartHTML: 0 EndHTML: 0 StartFragment: 0 EndFragment: 0 
<?xml version="1.0" encoding="utf-8"?>
<Mashup xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://schemas.microsoft.com/DataMashup">
    <Client>PBIDesktop</Client>
    <Version>2.30.4246.1721</Version>
    <MinVersion>2.21.0.0</MinVersion>
    <Culture>en-US</Culture>
    <SafeCombine>true</SafeCombine>
    <Items>
        <Query Name="Server">
            <Formula><![CDATA["text"]]></Formula>
            <LoadToReport>false</LoadToReport>
            <IsParameterQuery>false</IsParameterQuery>
            <IsDirectQuery xsi:nil="true" />
        </Query>
    ...
    </Items>
</Mashup>

So my theory is I can format my text file like this, stuff it into the clipboard in HTML format, and then paste it into Power Query. So far, I haven't been able to copy it to the clipboard as HTML. It only copies as text. It will not even paste into Power Query as text. It has to be HTML.

Any idea how I can get my text file into this format? I've been trying this Python code, but no luck yet.

Another option would be if I could edit the clipboard. You can pin data in clipboard history. If I could figure where this is stored, maybe I could just add my queries there?

Update:

I've given up on this approach. This article shows how the clipboard copies multiple formats at the same time. In my limited testing, I believe it is the "Microsoft Mashup Format," not the "HTML Format" that needs to be populated in order to paste into Power BI. This could also explain why the header in the HTML Format is not conformant and Microsoft doesn't care to fix it.

The InsideClipboard tool was essential for examining the clipboard after a copy to see what Microsoft was doing. The "Microsoft Mashup Format" has a binary header which seemed to be consistent between tests with a 2? byte length that changed. My Python isn't good enough to change the code so I ditched this approach. I'll put my current solution in a answer to this post.

Solution

  • A much simpler approach is to use VBA macros to create the queries from the text file in Excel Power Query, then manually copy the Excel queries to paste into Power BI Desktop. You can select all the queries in Excel at once from either the Queries & Connections pane or the Power Query Editor and copy them in one shot.

    Here is some VBA code to create the queries in a new file. It requires the file with the macro to have a table named "Data_sources" on a sheet named "Data sources" with at least 4 columns (Data Source Name, Data Source Query, Server, and Database):

    Sub CreateDataSourceQueries()
    Dim i As Integer
    Dim dswb As Workbook  'New Data Source Workbook
    Dim dst As ListObject 'Table with all data sources
    
    'Create new workbook
    Set dswb = Workbooks.Add
    dswb.ActiveSheet.name = "Data Source Queries"
    dswb.ActiveSheet.Range("A1").Value = "Data Source Queries"
    dswb.ActiveSheet.Range("A2").Value = "Server:"
    dswb.ActiveSheet.Range("A3").Value = "Database:"
    
    'Adding comment because code to set width isn't working
    dswb.ActiveSheet.Range("A5").Value = "To see queries, click: Data > Queries & Connections"
'    With Application.CommandBars("Queries and Connections")
'        .Visible = True
'        .Width = 400     'Doesn't seem to work, but maybe Microsoft will fix it?
'    End With
    
    'Create query in new book for each data source
    Set dst = ThisWorkbook.Sheets("Data sources").ListObjects("Data_sources")
    For i = 1 To dst.DataBodyRange.Rows.Count
        dswb.Queries.Add name:=dst.ListColumns("Data Source Name").DataBodyRange(i), _
                         Formula:=dst.ListColumns("Data Source Query").DataBodyRange(i)
    Next i
    
    'Add info on Server and Database being migrated
    dswb.ActiveSheet.Range("B2").Value = dst.ListColumns("Server Name").DataBodyRange(1)
    dswb.ActiveSheet.Range("B3").Value = dst.ListColumns("Database Name").DataBodyRange(1)
    
End Sub