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: I would like to get via:

                 1. DAX 

                 2. not using function Promote headers(at power query)

 

Thanks

 

 

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 

11 Replies

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

         

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

    DAX does not handle table manipulation in terms of headers, so you have no options in DAX

     

    In Power Query, either you click the Promote Headers button or you can use the M function Table.PromoteHeaders

     

    The only way to avoid this step is loading from a Database where this thing is unnecessary since headers are defined in the database itself

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

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

    Hi  Jyaulhaq 

     

    You can create a new calculated table with corrected headers using the DAX formula below:

    Note:  I have used a dummy table named 'Sample Data' please replace it with your actual table name.

     

     

    Steps:
    1. Go to Model View in Power BI.

    2. Click on New Table and paste the following DAX:

    SalesFinal = 
SELECTCOLUMNS (
    FILTER ( 'Sample Data', 'Sample Data'[Column1] <> "Country" ),
    "Country", 'Sample Data'[Column1],
    "Sales", 'Sample Data'[Column2],
    "Date", 'Sample Data'[Column3]
)

     

    Output:

     

     

     

     

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

    Why would not want to use Power Query? While it is possible with DAX, you will have to create a calculated table which takes storage and you will have to update the formula for each column added to the referenced table. It won't be automatic.

  • v-menakakota's avatar
    v-menakakota
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Jyaulhaq   , 

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    I would also take a moment to thank  danextian and FBergamaschi  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

    I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

    Best Regards, 
    Community Support Team 

      

     

    • v-menakakota's avatar
      v-menakakota
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Jyaulhaq ,

      Can you please confirm whether the issue has been resolved. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need. 

      Thank you.

  • Jyaulhaq's avatar
    Jyaulhaq
    Frequent Visitor
    1 year ago

    Do not get proper solution , I have n number of column and need to do dynamic header at dax level.

    • v-menakakota's avatar
      v-menakakota
      Icon for Community Support rankCommunity Support
      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 

      • v-menakakota's avatar
        v-menakakota
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi  @Jyaulhaq ,

        Can you please confirm whether the issue has been resolved. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need. 

        Thank you.