tbablue
Active Member
- Joined
- Apr 29, 2007
- Messages
- 488
- Office Version
- 365
- Platform
- Windows
Hi forum,
I'm trying to implement parameters into my Power Query. I can manage without issue when I enter a parameter - I can manage when I introduce a NullStep for a single parameter - but I'm struggling to implement multiple parameters with multiple NullSteps. Any assistance would be welcome.
This is my query in full...
I keep getting the following error message...
Expression.Error: A cyclic reference was encountered during evaluation.
Thanks again for your time, attention, and expertise. Most grateful.
I'm trying to implement parameters into my Power Query. I can manage without issue when I enter a parameter - I can manage when I introduce a NullStep for a single parameter - but I'm struggling to implement multiple parameters with multiple NullSteps. Any assistance would be welcome.
This is my query in full...
Power Query:
let
Source = #"RAW Cost Transaction Data Drop",
//Variables
//
Project = Project_Picker,
from = Picker_From,
to = Picker_To,
//Processing Data
//
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}}),
#"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Period", each "0" & _, type text}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Added Prefix", {{"Period", each Text.End(_, 2), type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Last Characters", {{"Year", type text}}, "en-GB"),{"Year", "Period"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Reporting Period"),
#"Duplicated Column" = Table.DuplicateColumn(#"Merged Columns", "Reporting Period", "Reporting Period - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Reporting Period - Copy", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Control Account ID", "Project", "Text Between Delimiters", "Activity", "Project Description", "Transaction Date", "Time", "WBS Description", "Cost Type", "Cost Object", "Cost Obj Desc.", "Description", "Cost Component", "Cost Comp Desc.", "Employee", "Employee name", "Labor Type", ".", "Origin", "Posting Type", "Document", "._1", "Quantity", "Units", "Project Cost", "Currency", "Financial Document", "._2", "._3", "._4", "._5", "Document Number", "Post to Finance", "Transaction Origin", "Financial Transaction", "Cost Control Period", "Reporting Period", "Reporting Period - Copy", "Hours Control Period (wk)", "..", ".._6", "Include?"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Hours Control Period (wk)", "..", ".._6", "Include?", "Cost Control Period", "Post to Finance", "Financial Document", "._2", "._3", "._4", "._5", "Document Number", ".", "Labor Type", "Cost Component", "Cost Object", "Time", "Text Between Delimiters", "Activity", "Project Description"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Project] = Project)),
#"Filtered Rows1" = Table.SelectRows(NullStepTo, each [#"Reporting Period - Copy"] >= from),
#"Filtered Rows2" = Table.SelectRows(NullStepTo, each [#"Reporting Period - Copy"] <= from),
//NullSteps
//
NullStepProject = if Project_Picker<>null then #"Filtered Rows" else #"Removed Columns",
NullStepFrom = if Picker_From<>null then NullStepProject else #"Filtered Rows2",
NullStepTo = if Picker_To <> null then NullStepFrom else #"Filtered Rows2"
in
NullStepTo
I keep getting the following error message...
Expression.Error: A cyclic reference was encountered during evaluation.
Thanks again for your time, attention, and expertise. Most grateful.
Last edited by a moderator: