Forum Discussion

Marty_H's avatar
Marty_H
Frequent Visitor
4 years ago

Column averages table with ascending values and dynamic date slicer

Hi everyone, I have a table that contains data with daily values like this: Now I want to create a table with the averages in ascending order that adjusts itself when moving the date ...
Need Help
Marty_H's avatar
Marty_H
Frequent Visitor
4 years ago

I added some data files to my original message for you.

If I unpivot my data, the dates (rows) would become the columns.
If I want to add them to a table, are you sure that extra data (more data with every day added) will be added with a refresh?
Because I believe new columns don't get added to table visuals automatically after a refresh or am I wrong here?

I'm gonna try it out.
In general what the treemap visual does in my pbix file is what I would like to see in a table visual with the names in the columns of the original data and next to it the exact percentages.
Now I need to hover over all the squares in the treemap visual to see the percentages.

This doesn't provide me with an accurate quick overview of these exact percentages.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

Here is the query to combine all your files and then unpivot them to make them usable.

 

 

 

let
    Bron = Folder.Files("C:\Users\xxx\Downloads"),
    #"Filtered Rows" = Table.SelectRows(Bron, each Text.Contains([Name], "HeatMapData") ),
    #"Aangepaste functie aanroepen1" = Table.AddColumn(#"Filtered Rows", "Bestand transformeren", each #"Bestand transformeren"([Content])),
    #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Aangepaste functie aanroepen1", {"Name", "Source.Name"}),
    #"Andere kolommen verwijderd1" = Table.SelectColumns(#"Namen van kolommen gewijzigd1", {"Source.Name", "Bestand transformeren"}),
    #"Expanded Bestand transformeren" = Table.ExpandTableColumn(#"Andere kolommen verwijderd1", "Bestand transformeren", Table.ColumnNames(#"Andere kolommen verwijderd1"{0}[Bestand transformeren])),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Bestand transformeren", {"Source.Name", "Day"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
    #"Changed Type"

 

see attached