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 should display the code from data table in the correct day.month, is this possible please?

Tables:
Table1 = Months (January-December) & ref (numbers 1-12)
Table2 = DayNum (day numbers 1-31)
Table3 = Data: Date, code (Sick, Late etc)

I'd like any code to show the output table in the corresponding day/month, like below.
Black fields to be left empty
(if possible colour missing month days black i.e. 29th-31st Feb, 31st April etc)

if 2 codes have been entered for the same day (i.e. late & sick etc) a deperate Dual code should show.

Is this even possible please?

 

Thanks in advance

8 Replies

    • 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"