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,

Please excuse my poor formatting, I’m using a mobile at the moment.

Basically the table output should be:

................. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10 etc
January. Sick
February
March. ..............Late
April
Etc

Really sorry about the poor format
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

I would ignore your first two tables and just work with your 3rd table and do something 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}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Day",{"Date"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Day", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Day", type text}}, "en-US")[Day]), "Day", "Code")
in
    #"Pivoted Column"
  • 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.