Creating a Calculated Table for Item and Measure for All Months in Date Table.

[ctaylor]

Hello,

I am looking to make a computed table using a date table and a list of properties that shows the occupancy % for each property and each month. 

The table would essentially look like this:

I have tried what I know to pivot/unpivot data in the two tables but I can't figure out how to essentially duplicate the calendar month for every property in the list (about 200). 

What the best way to create this summary table?

Thanks! 

[edhans · Accepted solution]

I think you will need to use a Cross Join trick in Power Query where you basically drop one table into a new column, then expand.

This code will create a table of 12 months all using the first of the month. Jan 1, 2020, Feb 1, 2020, etc..

let
    Source = Table.FromList({1..12}, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Convert to Date" = Table.TransformColumns(Source, {{"Date", each #date(2020,_,1), type date}})
in
    #"Convert to Date"

 Then do the following:

1. This is the Date query created with the M code above
2. Add a new custom column and simply reference the Date query. Press ok.
3. You will have a Date column that has Table in yellow. If you click in the white area next to the word table you can see...
4. all of the dates
5. click the Expand button and you get the following. All 12 dates in every single Property line.

If you want to get fancy, you could do all of that in your main table without a new query. The M code is a bit tedious to type in and harder to follow, but same result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmAyEUwmgclkpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t]),
    #"Added Date Column" = 
        Table.AddColumn(
            Source, "Date", 
            each 
                Table.TransformColumns(
                        Table.FromList({1..12}, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
                        {{"Date", each #date(2020,_,1), type date}}
                    )
                ),
    #"Expanded Date" = Table.ExpandTableColumn(#"Added Date Column", "Date", {"Date"}, {"Date"})
in
    #"Expanded Date"

The #"Added Date Column" is the relevant step.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[ctaylor]

@edhans 

Thank you!

I didn't need to go with the fancy code at the bottom, just the concept of the first section was what I needed.  I had done this before for another reason but I couldn't remember what to do to make the whole of the calendar apply to each property.

[edhans]

Excellent @ctaylor - glad I was able to help out.