Forum Discussion

Zod's avatar
Zod
Regular Visitor
8 years ago

Query - Days Top row, Months Down left

Hi All, I'm not sure if this is the correct place to post, I'm using Power Query.   I'm looking to adjust a query to display days along the top and month down the left column, The main table sho...
Zod's avatar
Zod
Regular Visitor
8 years ago

This is to create an attendance tracker.

One of the outputs needs to show the year layed out this way. (days top, months down left)

 

If there is a better way to do this, I'm happy to look at that.

unfortunatley the corporate network is locked down, power query/excel is advanced as it gets. 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

So, I made a copy of the query, kept your data in its original form removing all the fancy steps like this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMLRQ0lEKzkzOVorVAYkZwcR8EktSoWLGcHX5uaklGZl56UqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Code", type text}})
in
    #"Changed Type"

Then these two DAX columns:

 

Month = FORMAT([Date],"mmmm")

Day = DAY([Date])

 

 

 

Then I could create a Matrix visualization like this:

 

  • Zod's avatar
    Zod
    Regular Visitor
    8 years ago

    Hi Greg,

    Thanks so much for your help with this, after a bit of fiddling, I'm almost there.

     

    As more data is entered, I'm getting multiple months appear, is there anyway to prevent this?

     

    The link below will take you to the file

    https://1drv.ms/x/s!AsqmANjuHvipiCEyTdycKK7CLLZ7