Promote Headers via dax or not use function Promote headers

[Jyaulhaq]

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

[v-menakakota]

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 

[danextian]

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.

[rohit1991]

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:

[FBergamaschi]

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

[MohamedFowzan1]

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]

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

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.