How to Export USQL Query to Excel Sheet using PowerQuery?

[MostafaHussein]

Assume you've a USQL Query like below figure and you want to be dynamically exported to excel for any analytical purpose "inspired by @Mohamed_Hamdy "

requirements:

• Endpoint `/api/v2/userSessionQueryLanguage/table`
• Endpoint parameter: ?query={USQL Query}
• API Permission: DTAQLAccess
• PowerQuery Code explained at the end

1st you can test the endpoint from browser to ensure you're getting the proper data from your query then simply open new excel sheet, then go to data tab and pick Get & Transform Data > From Web, there's a simple wizard that helps you using the endpoint.

Note: you can authenticate from URL Query Parameter but i don't prefer that for security concerns, take a look in the below code snippet and see how to use the API Token from request headers.

let

    Source = Json.Document(Web.Contents({endpoint} & "?query={USQL Query}", [Headers=[Accept="application/json", #"Content-Type"="application/json", #"Accept-Charset"="utf-8", Authorization="Api-Token {API Token}"]])),
    // getting the Values Content into Variable
    values1 = Source[values],
    
    // creating table from `values1`
    SessionsTable = Table.FromColumns(values1),
    
    // replace columns to be rows and the opposit as each row represents multiple values(columns) for single data terms like `userId` as example
    #"Transposed Table" = Table.Transpose(SessionsTable),

    // in the next rows `Extracted Values` we started expanding each column to be readable and meaningful as some columns datatype may be type of list or any datatype so we convert all to string
    #"Extracted Values" = Table.TransformColumns(#"Transposed Table", {"Column3", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column4", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Column5", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Column6", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Extracted Values4" = Table.TransformColumns(#"Extracted Values3", {"Column7", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    // this is example for extracting & transforming value in some column in one step as it's a timestamp and needs to be formated like `10/13/2023 6:01:45 PM` to be readable
    #"Extracted Values5" = Table.TransformColumns(#"Extracted Values4", {{"Column2", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, _/1000), type datetime}}),

    #"Renamed Columns" = Table.RenameColumns(#"Extracted Values5",{{"Column1", "User ID"}, {"Column2", "Start Time"}, {"Column4", "Interaction(s)"}, {"Column6", "Payment Status"}, {"Column8", "Browser Type"}, {"Column9", "Browser Version"}, {"Column10", "City"}, {"Column12", "Gender"}})

in
    // finally! returning the renamed column with its transformed values
    #"Renamed Columns"

some of columns you got may be in type of list and you want to show them without expanding them to new rows so you can do column transformation like the below code snippet:

#"Extracted Values" = Table.TransformColumns(#"Transposed Table", {"Column3", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),

this will transform your list column to single comma separated line and you can replace comma with anything you want like new line using "#(lf)" and there's an example if you want to make it from visual editor

also for there's a tip for transforming timestamps to real date to be more meaningful and readable

#"Extracted Values5" = Table.TransformColumns(#"Extracted Values4", {{"Column2", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, _/1000), type datetime}}),

BR,
Mostafa Hussein.