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))