Learn from the best! Meet the four finalists headed to the FINALS of the Power BI Dataviz World Championships! Register now
Hi,
is it possible to use a parameter to call a step in the query?
There is a filtering step in my query, that on a few occasions I want to disable. My idea was to create a parameter which contains the string value of one of the two previous step names, so that I can choose whether I want my query to use the filtered table, or the un-filtered one (from the step previous to the filter step).
But I'm getting the following error: Expression.Error: We cannot convert the value "#"Filtered Rows"" to type Table.
I'm showing an example script below.
Example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEKSk1RAFKGOiZKsTrRSk6JeUAIFIhMzcnJLwcyDHTMTcFS/kWJeekgLXCGEVg8IKc0F2GQgY4RUHksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Color = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Color", type text}, {"Price", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fruit", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Color] = "Red ")),
#"Multiplied Column" = Table.TransformColumns(#"Filter OnOff", {{"Price", each _ * 1.2, type number}})
in
#"Multiplied Column"
Solved! Go to Solution.
let
Source = Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"i45WciwoyElV0lEKSk1RAFKGOiZKsTrRSk6JeUAIFIhMzcnJLwcyDHTMTcFS/kWJeekgLXCGEVg8IKc0F2GQgY4RUHksAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ Fruit = _t, Color = _t, Price = _t ]
),
ChangedType = Table.TransformColumnTypes (
Source,
{ { "Fruit", type text }, { "Color", type text }, { "Price", type number } }
),
SortedRows = Table.Sort ( ChangedType, { { "Fruit", Order.Ascending } } ),
FilteredRows = Table.SelectRows ( SortedRows, each ( [Color] = "Red " ) ),
MultipliedColumn = Table.TransformColumns (
if DoSomething = "FilteredRows"
then FilteredRows
else
if DoSomething = "SortedRows"
then SortedRows
else ChangedType,
{ { "Price", each _ * 1.2, type number } }
)
in
MultipliedColumn
let
Source = Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"i45WciwoyElV0lEKSk1RAFKGOiZKsTrRSk6JeUAIFIhMzcnJLwcyDHTMTcFS/kWJeekgLXCGEVg8IKc0F2GQgY4RUHksAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ Fruit = _t, Color = _t, Price = _t ]
),
ChangedType = Table.TransformColumnTypes (
Source,
{ { "Fruit", type text }, { "Color", type text }, { "Price", type number } }
),
SortedRows = Table.Sort ( ChangedType, { { "Fruit", Order.Ascending } } ),
FilteredRows = Table.SelectRows ( SortedRows, each ( [Color] = "Red " ) ),
MultipliedColumn = Table.TransformColumns (
if DoSomething = "FilteredRows"
then FilteredRows
else
if DoSomething = "SortedRows"
then SortedRows
else ChangedType,
{ { "Price", each _ * 1.2, type number } }
)
in
MultipliedColumn
Thanks! I will accept this as a solution.
But do you think there would be a more elegant solution? In order to do what you suggest I have to maintain the manual string of the step names in two places, both in the list of values in the parameter and then in the actual script again.
| User | Count |
|---|---|
| 13 | |
| 8 | |
| 7 | |
| 5 | |
| 5 |