Forum Discussion

Matt_Paz's avatar
Matt_Paz
Copper Contributor
Jun 18, 2025

Create table using LET based on column values

Hi all Hope you can help. I am trying to create an output table based on another tables column data.   Example: Original Data Table: Task                              Target          Group 1   ...
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Jun 19, 2025

This Lambda will unpivot a matrix by specifying rows, columns, and values. Once the data is flat, you can filter as needed.

UnPivotMλ = LAMBDA(rows, columns, values,
        LET(
            i, ROWS(rows),
            j, COLUMNS(rows),
            c, COLUMNS(columns),
            s, TOCOL(SEQUENCE(, j) * SEQUENCE(c, , 1, 0)),
            row_labels, WRAPROWS(TOCOL(CHOOSECOLS(rows, s)), j),
            attribute, TOCOL(CHOOSEROWS(columns, SEQUENCE(i, , 1, 0))),
            v, TOCOL(values),
            HSTACK(row_labels, attribute, v)
        )
    );

It's a good idea to study the PQ solution Riny_van_Eekelen​ provided because it's an essential skill.

  • Matt_Paz's avatar
    Matt_Paz
    Copper Contributor
    Jun 19, 2025

    Thank you for your reply and assistance!

    Sadly Power Query & Macros is blocked in my work organisation so I am restricted to LET.

    When implementing your example, I get a few #N/As on columns and also the issue where if the value is blank, a 0 is being displayed.

     

    Can this formula be modified to work on the following logic?

     

    If the value is empty, the whole row is not repeated?

     

    For example these highlighted would not be displayed and the overall order would still align.

     

     

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Jun 20, 2025

      The attached removes the rows with 0s.

      For the columns, the width must match the width of the selected values (even if the columns are blank). If your example presents a differently arranged header then a sample may be needed.

      • Matt_Paz's avatar
        Matt_Paz
        Copper Contributor
        Jun 20, 2025

        Hi Patrick

        Thanks for your time supporting me here.

        I attach a live example of what dataset / table structure I am trying to apply this to.

        You will see the top table (original data table) and the desired output I am looking for underneath.

        If the Team result is empty, no data is shown in the desired output.

        Your support is much appreciated

        Matt

Resources