Edit

Share via

Tutorial: Create a query with the visual query builder

  • Article

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

Create and save a query with the visual query builder in the Microsoft Fabric portal.

Use the visual query builder

  1. From the Home tab of the ribbon, select New visual query.

    Screenshot of the ribbon, showing where to select New visual query.

  2. Drag the fact_sale table from the Explorer to the query design pane.

    Screenshot of the explorer pane next to the query design pane, showing where to drag the table.

  3. Limit the dataset size by selecting Reduce rows > Keep top rows from the transformations ribbon.

    Screenshot of the Reduce rows drop-down menu, showing where to select the Keep top rows option.

  4. In the Keep top rows dialog, enter 10000.

  5. Select OK.

  6. Drag the dimension_city table from the explorer to the query design pane.

  7. From the transformations ribbon, select the dropdown next to Combine and select Merge queries as new.

    Screenshot of the transformations ribbon with the Combine drop-down menu open, showing where to select Merge queries as new.

  8. On the Merge settings page:

    1. In the Left table for merge dropdown list, choose dimension_city

    2. In the Right table for merge dropdown list, choose fact_sale

    3. Select the CityKey field in the dimension_city table by selecting on the column name in the header row to indicate the join column.

    4. Select the CityKey field in the fact_sale table by selecting on the column name in the header row to indicate the join column.

    5. In the Join kind diagram selection, choose Inner.

    Screenshot of the Merge dialog box, showing where to find table names and CityKey fields.

  9. Select OK.

  10. With the Merge step selected, select the Expand button next to fact_sale on the header of the data grid then select the columns TaxAmount, Profit, and TotalIncludingTax.

    Screenshot of the table with Merge selected and TaxAmount, Profit, and TotalIncludingTax selected.

  11. Select OK.

  12. Select Transform > Group by from the transformations ribbon.

    Screenshot of the transformations ribbon, showing where to select Group by from the Transform drop-down menu.

  13. On the Group by settings page:

    1. Change to Advanced.

    2. Group by (if necessary, select Add grouping to add more group by columns):

      1. Country
      2. StateProvince
      3. City

    3. New column name (if necessary, select Add aggregation to add more aggregate columns and operations):

      1. SumOfTaxAmount
        1. Choose Operation of Sum and Column of TaxAmount.
      2. SumOfProfit
        1. Choose Operation of Sum and Column of Profit.
      3. SumOfTotalIncludingTax
        1. Choose Operation of Sum and Column of TotalIncludingTax.

    Screenshot of the Group by settings page with the correct values entered and selected.

  14. Select OK.

  15. Right-click on Visual query 1 in the Explorer and select Rename.

    Screenshot showing where to right select on the new visual query in the Explorer pane, and where to select Rename.

  16. Type Sales Summary to change the name of the query.

  17. Press Enter on the keyboard or select anywhere outside the tab to save the change.

Next step

Tutorial: Analyze data with a notebook