Forum Discussion

Jyaulhaq's avatar
Jyaulhaq
Frequent Visitor
1 year ago
Solved

Promote Headers via dax or not use function Promote headers

Hi, I have Sales Table country wise : how can I create new table with First row as header using    1. DAX     2. not using function Promote Headers(at Power query) Output:   Note: ...
Need Help
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    Hi Jyaulhaq ,

    Thank you for the update. DAX can’t dynamically promote headers when you have n number of columns. Any DAX approach would require listing each column manually, so it won’t update automatically if columns change.


    If you need it to work dynamically, the only option is to do it in Power Query or make sure the source already has the correct headers before loading.

    Best Regards, 
    Community Support Team 

MohamedFowzan1's avatar
MohamedFowzan1
Icon for Super User rankSuper User
1 year ago

Hi Jyaulhaq 
Not sure why you would need a requirement like this or the exact use case.

 

To my undertsanding, you would not be able to use DAX to dynamically promote headers

If absolutely necessary,
How about creating an entirely new table with the below dax and you could hide the original table

FilteredTable =
FILTER(
    SELECTCOLUMNS(
        RawTable,
        "Country", RawTable[Column1],
        "Sales", RawTable[Column2],
        "Date", RawTable[Column3]
    ),
    NOT (
        [Country] = "Country"
        || [Sales] = "Sales"
        || [Date] = "Date"
    )
)
Jyaulhaq's avatar
Jyaulhaq
Frequent Visitor
1 year ago

MohamedFowzan1  Thanks for your reply, 

It can work on my example above but manytimes I got data like:

or 

at this time, "Country", RawTable[Column1], is not going to work.

Could you please do needful ? Thanks

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Icon for Super User rankSuper User
    1 year ago

    FilteredTable =

    FILTER(
        SELECTCOLUMNS(
            RawTable,
            "City", RawTable[Column1],
            "Country", RawTable[Column2],
            "Sales", RawTable[Column3],
            "Date", RawTable[Column4]
        ),
        NOT ( [City] = "City"
            || [Country] = "Country"
            || [Sales] = "Sales"
            || [Date] = "Date"
        )
    )

    OR

    FilteredTable =
    FILTER(
        SELECTCOLUMNS(
            RawTable,
            "City", RawTable[Column1],
            "Country", RawTable[Column2],
            "Country Code", RawTable[Column3],
            "Sales", RawTable[Column4],
            "Date", RawTable[Column5]
        ),
        NOT ( [City] = "City"
            || [Country] = "Country"
            || [Country Code] = "Country Code"
            || [Sales] = "Sales"
            || [Date] = "Date"
        )
    )

    I understand and believe the table structure cant change so the above should suffice and to reiterate, it would be better to use the Make first row as Headers option and this method is not a dynamic solution but a work around.

    Thank you

    Found this useful? 💡 Give a Kudo and mark as solution to guide others.