Forum Discussion
Helper IData Manipulation, Fees and dates in a row
Hi guys!
I have data shown like this:
Fee.1 is tied to Date.1
Fee.2 is tied to Date.2
Can anyone show me a way to link these pieces to each other or manipulate it into a somewhat similar format below?
I have been manipulating it another way by unpivoting the 4 columns then adding a custom column. However I could not get the formula to work (below) and figured you guys might know a more basic solution.
= if [Attribute] = "Fee.1" and [Attribute.1] = "Date.2" then "0" else [Fee] + if [Attribute] = "Fee.2" and [Attribute.1] = "Date.2" then "0" else [Fee]
Thanks!
I would recommend to add this code as a new query and replace "Table1" by "FAM".
If you want to add the code within your FAM query, then you already have a source step and you should use my code from the second step onwards, with the first new step referencing your current last step in the query. And you need to add a comma at the end of the last line of your current query.
It is quite logical that you get a cyclic reference if you have a line Source = FAM in a query with the name FAM, as it is referencing itself. You can't use the output from a query as the input for the same query.
Exception: in Excel you can output query results to an Excel table and use that table as input for the same query. In that case there is no cyclic reference as the query is not referencing itself, but the table in Excel which are different objects: the query result is not the Excel table; the query results are output to an Excel table.
7 Replies
- MarcelBeug
Community Champion
You can merge the corresponding columns to nested records, then unpivot and expand the records:
let Source = Table1, One = Table.CombineColumns(Source, {"Fee.1", "Date.1"},each Record.FromList(_,type[Fee = Int64.Type, Date = date]),"1"), Two = Table.CombineColumns(One, {"Fee.2", "Date.2"},each Record.FromList(_,type[Fee = Int64.Type, Date = date]),"2"), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Two, {"name"}, "Attribute", "Value"), #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Other Columns", "Value", {"Fee", "Date"}, {"Fee", "Date"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Value",{"Attribute"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Fee", Int64.Type}, {"Date", type date}}) in #"Changed Type"
charles_oI am getting the error below.
Expression.Error: A cyclic reference was encountered during evaluation.
- MarcelBeug
That's because a cyclic reference was encountered during evaluation.
Please understand we are no clairvoyants.