Raw Power Query Table as Visual

[cerebro]

Dear community,

I have a perfect query where column names are dynamic dates and the order is descending except for the total line

In the desktop version of power bi as a matrix or table visual it messes everything up, the order of the lines is wrong, the column names change every week so that a have to rebuild this visual every week with new column names:

 

is there any way of displaying the whole table from power query in a desktop report ? No matrix and no table visual, just raw data?

Thank you in advance!

[Anonymous · Accepted solution]

Hi  @cerebro ,

Here are the steps you can follow：

1. Select [2024.10.13], [2024.10.6], [2024.10.20],[%week] -- Transform – Unpivot Columns.

2. Add Column – Custom Column – enter code.

if [Attribute] = "%week" then null else [Attribute]

3. Select [Custom] – Change Type – Date Time

4. Select [Custom] – Display the specified Format

5. Create calculated column.

Rank =
IF(
    [Custom]=BLANK(),0,
RANKX(
    'True_Table',[Custom],,DESC,Dense))

6. Create measure.

Measure =
IF(
    MAX('True_Table'[Rank])<=2,SUM('True_Table'[Value]),BLANK())

7. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[cerebro]

many thanks, that works!

[Anonymous]

Hi  @cerebro ,

As far as I know, Power BI Desktop sorts by default in alphabetical order. You can try setting an index in Power Query and sort the [Category] according to the index.

In Power Query -- Add Column – Index Column – From 1

Select – [Category] – Column tools – Sort by column – [Index]

Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cerebro]

thank you very much for the hint with the indexing.

Do you have an idea how to solve my other problem, that the column names change every week, this week the columns are "2024.10.13" and "2024.10.20" and i have to build up the matrix visual every week once again, because the column "2024.10.6" will not be found anymore (dynamically two last weeks).

Thank you again.