none
MissingField.Ignore in Table.Unpivot function RRS feed

  • Question

  • Hello,

    I'm trying to set up a generic set of transformations that handle similar but not identical tables.  I'm stuck with an unpivot function where there are generally three columns, Say ColA1, ColB1, ColC1 to unpivot but sometimes there may be two.  The two will always be a subset of the three such as ColA1 and ColC1.  Ideally I could set up the unpivot function and just ignore the column that is not there.

    Is there something similar to MissingField.Ignore that can be used with the Unpivot function, or another way to accomplish this? Unfortunately this is a problem I'll have multiple times.

    Thanks!

    Thursday, August 31, 2017 5:25 AM

Answers

  • Hi there. You could write a small M expression that checks which of the three columns actually exist in the table, and then pass that list to Table.Unpivot.

    For your example above, it would be something like this:

    = Table.Unpivot(Source, List.Intersect({Table.ColumnNames(Source), {"ColA1", "ColB1", "ColC1"}}), "Attribute", "Value")

    Ehren

    • Marked as answer by TDUB11 Thursday, August 31, 2017 8:39 PM
    Thursday, August 31, 2017 4:58 PM
    Owner

All replies

  • Hi there. You could write a small M expression that checks which of the three columns actually exist in the table, and then pass that list to Table.Unpivot.

    For your example above, it would be something like this:

    = Table.Unpivot(Source, List.Intersect({Table.ColumnNames(Source), {"ColA1", "ColB1", "ColC1"}}), "Attribute", "Value")

    Ehren

    • Marked as answer by TDUB11 Thursday, August 31, 2017 8:39 PM
    Thursday, August 31, 2017 4:58 PM
    Owner
  • If there are fixed columns in addition to the variable columns, another option would be to use Table.UnpivotOtherColumns.
    Thursday, August 31, 2017 6:05 PM
  • Thanks! I'll give this a go.  I tried to get the List.Intersect to work last night but looking where you have your curly braces, mine were certainly in the wrong spots!
    Thursday, August 31, 2017 6:06 PM