Data Manipulation, Fees and dates in a row

[charles_o]

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!

[MarcelBeug]

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_o]

I 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.

[MarcelBeug]

Maybe the name of your query is Table1, which conflicts with Table1 in the Source step.

[charles_o]

I am confused- is "Table1" not to be swapped for the name of my query? 

My query is named "FAM".My column names I want to join are: Date.1, Date.2, Fee.1, Fee.2 and they are in query "FAM"

When I use it with the "Source = Table1"

Expression.Error: The name 'Table1' wasn't recognized.  Make sure it's spelled correctly.

Which makes sense because there is no Table1, but if I rename it to my query "Source = FAM"

Expression.Error: A cyclic reference was encountered during evaluation.

 This does not make sense to me, sorry if this is basic I am very new to Power BI

[MarcelBeug · Accepted solution]

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.

[charles_o]

Thanks!

Sorry it took me a bit- just getting started with Power BI.