let
Source =
Excel.Workbook(
File.Contents("C:\FilterParameter.xlsx"),
null, true),
FilterDay_Table =
Source{[Item="FilterDay",Kind="Table"]}[Data],
ChangedType =
Table.TransformColumnTypes(
FilterDay_Table,
{{"Parameter", type text}}
),
Output =
ChangedType{0}[#"Parameter"]
in
Output
let
Source =
Sql.Database(
"localhost",
"adventure works dw",
[Query="select DateKey, EnglishDayNameOfWeek
from DimDate"]),
FilteredRows =
Table.SelectRows(Source,
each ([EnglishDayNameOfWeek] = FilterDay)
)
in
FilteredRows
let
Source =
Table.SelectRows(
Sql.Database(
"localhost",
"adventure works dw",
[Query="select DateKey,
EnglishDayNameOfWeek
from DimDate"]
),
each ([EnglishDayNameOfWeek] = FilterDay)
)
in
Source
let
Source =
Sql.Database(
"localhost",
"adventure works dw",
[Query="
select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek='" & FilterDay & "'"
]
)
in
Source
let
DayAsStep = FilterDay,
Source =
Sql.Database(
"localhost",
"adventure works dw",
[Query="
select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek='" & DayAsStep & "'"
]
)
in
Source
let
Source = Sql.Database("localhost", "adventure works dw"),
ParamRecord = [FilterParameter=FilterDay],
Query = Value.NativeQuery(
Source,
"select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek=@FilterParameter",
ParamRecord)
in
Query
let
ExcelSource =
Excel.Workbook(
File.Contents("C:\FilterParameter.xlsx")
, null, true),
FilterDay_Table =
ExcelSource{[Item="FilterDay",Kind="Table"]}[Data],
ChangedType =
Table.TransformColumnTypes(FilterDay_Table,
{{"Parameter", type text}}),
FilterDayStep =
ChangedType{0}[#"Parameter"],
SQLSource = Sql.Database(
"localhost",
"adventure works dw",
[Query="
select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek='"
& FilterDayStep &
"'" ])
in
SQLSource