Prep Migration: Tableau Prep to Power Query / Dataflows / dbt¶

// Tableau Prep: Input → Wildcard Union → *.csv from folder

// Power Query M:
let
    Source = Folder.Files("\\server\data\monthly"),
    FilteredCSV = Table.SelectRows(Source, each [Extension] = ".csv"),
    CombinedData = Table.Combine(
        Table.AddColumn(FilteredCSV, "Data",
            each Csv.Document([Content], [Delimiter=",", Encoding=65001])
        )[Data]
    )
in
    CombinedData

// Tableau Prep: Join Step → Left Join on CustomerID

// Power Query M:
let
    Orders = ...,
    Customers = ...,
    Merged = Table.NestedJoin(
        Orders, {"CustomerID"},
        Customers, {"CustomerID"},
        "CustomerDetails",
        JoinKind.LeftOuter
    ),
    Expanded = Table.ExpandTableColumn(Merged, "CustomerDetails",
        {"CustomerName", "Region", "Segment"})
in
    Expanded

// Tableau Prep: Pivot Rows to Columns on month columns
// Source table: Product, Jan, Feb, Mar, Apr, ...

// Power Query M:
let
    Source = ...,
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")
in
    Unpivoted
// Result: Product | Month | Sales (one row per product per month)

// Tableau Prep: Aggregate → Group by Region, SUM(Sales), AVG(Profit)

// Power Query M:
let
    Source = ...,
    Grouped = Table.Group(Source, {"Region"}, {
        {"Total Sales", each List.Sum([Sales]), type number},
        {"Avg Profit", each List.Average([Profit]), type number},
        {"Order Count", each Table.RowCount(_), Int64.Type}
    })
in
    Grouped

// Tableau: Prep flow runs → publishes data source → extract refreshes → workbook updated

// Power BI / Fabric:
// 1. Fabric Pipeline runs dbt models (updates Gold tables)
// 2. Dataflow Gen2 refreshes (if used for additional shaping)
// 3. Semantic model refresh triggers (Import mode) or data is immediately available (Direct Lake)
// 4. Subscriptions send updated reports

// Configuration:
// - Fabric Pipeline: scheduled via Fabric or ADF
// - Dataflow: scheduled in workspace settings
// - Dataset: "Refresh after dataflow" option or scheduled independently

// Connect to SQL Server
= Sql.Database("server.database.windows.net", "mydatabase")

// Filter rows
= Table.SelectRows(Source, each [Amount] > 0 and [Date] >= #date(2024, 1, 1))

// Add calculated column
= Table.AddColumn(Source, "Revenue", each [Qty] * [Price], type number)

// Rename columns
= Table.RenameColumns(Source, {{"old_name", "new_name"}, {"old2", "new2"}})

// Change data types
= Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", type number}})

// Remove columns
= Table.RemoveColumns(Source, {"UnneededCol1", "UnneededCol2"})

// Replace values
= Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Amount"})

// Merge (join) tables
= Table.NestedJoin(Orders, {"CustID"}, Customers, {"CustID"}, "Cust", JoinKind.LeftOuter)

// Expand merged table
= Table.ExpandTableColumn(Merged, "Cust", {"Name", "Region"})

// Append (union) tables
= Table.Combine({Table1, Table2, Table3})

// Group by (aggregate)
= Table.Group(Source, {"Region"}, {{"Total", each List.Sum([Amount]), type number}})

// Unpivot columns
= Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")

// Pivot column
= Table.Pivot(Source, List.Distinct(Source[Month]), "Month", "Sales", List.Sum)

// Conditional column
= Table.AddColumn(Source, "Tier", each if [Amount] > 1000 then "High" else "Low")

// Remove duplicates
= Table.Distinct(Source, {"KeyCol"})

// Sort
= Table.Sort(Source, {{"Date", Order.Descending}})