Microsoft Power BI

# No Power BI Desktop:
1. Get Data > SQL Server
2. Server: localhost\SQLEXPRESS
3. Database: AdventureWorks
4. Data Connectivity mode: Import/DirectQuery
5. Advanced options:
   - SQL statement: SELECT * FROM Sales.SalesOrderHeader
   - Command timeout: 30 minutes

# Connection String exemplo:
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

# Total Sales (Measure)
Total Sales = SUM(Sales[Amount])

# Sales YTD (Year to Date)
Sales YTD = TOTALYTD([Total Sales], Calendar[Date])

# Sales Previous Year
Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))

# Growth %
Growth % = DIVIDE([Total Sales] - [Sales PY], [Sales PY], 0)

# Top 10 Products
Top 10 Products = 
CALCULATE(
    [Total Sales],
    TOPN(10, ALL(Product[ProductName]), [Total Sales])
)

# Running Total
Running Total = 
CALCULATE(
    [Total Sales],
    FILTER(
        ALLSELECTED(Calendar[Date]),
        Calendar[Date] <= MAX(Calendar[Date])
    )
)

# Transformação de dados básica
let
    Source = Sql.Database("localhost", "AdventureWorks"),
    Sales = Source{[Schema="Sales",Item="SalesOrderHeader"]}[Data],
    FilteredRows = Table.SelectRows(Sales, each [OrderDate] >= #date(2023, 1, 1)),
    AddedColumn = Table.AddColumn(FilteredRows, "Year", each Date.Year([OrderDate])),
    GroupedRows = Table.Group(AddedColumn, {"Year"}, {{"Total Sales", each List.Sum([TotalDue]), type number}})
in
    GroupedRows

# Conectar a API REST
let
    Source = Json.Document(Web.Contents("https://api.example.com/sales")),
    ConvertedToTable = Table.FromRecords(Source),
    ExpandedColumns = Table.ExpandRecordColumn(ConvertedToTable, "data", {"id", "amount", "date"})
in
    ExpandedColumns

# Autenticação
curl -X POST https://login.microsoftonline.com/{tenant-id}/oauth2/token \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=client_credentials&client_id={client-id}&client_secret={client-secret}&resource=https://analysis.windows.net/powerbi/api"

# Listar workspaces
curl -X GET https://api.powerbi.com/v1.0/myorg/groups \
  -H "Authorization: Bearer {access-token}"

# Refresh dataset
curl -X POST https://api.powerbi.com/v1.0/myorg/datasets/{dataset-id}/refreshes \
  -H "Authorization: Bearer {access-token}" \
  -H "Content-Type: application/json"

# Export report
curl -X POST https://api.powerbi.com/v1.0/myorg/reports/{report-id}/ExportTo \
  -H "Authorization: Bearer {access-token}" \
  -H "Content-Type: application/json" \
  -d '{"format": "PDF"}'