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

Hi Greg,

 

Thanks for this,

 

I initially got an error around date.monthname wasn't recognised?

I removed this setp to see how things would look, this looks like it could do the trick, except.

I'm looking for numbers 1-31 to apper perminantly across the top and months to appear perminantly down the left.

Code would update when/if there is corrisponding data, otherwise remain blank

 

((Not sure if this matters but all dates are in UK format))

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

So, one question that I have is why do you want it this way in Power Query? It would seem like this would be better down in a Matrix visualization and that you should just import your data the way it is without transforming it. Just a thought. There may be a perfectly good reason, but I don't understand what that is.

  • 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