Query - Days Top row, Months Down left

[Zod]

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

[Greg_Deckler]

I do not see anything below as you indicated.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Zod]

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]

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]

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]

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]

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]

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]

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