Forum Discussion
Creating a Calculated Table for Item and Measure for All Months in Date Table.
- 6 years ago
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:
- This is the Date query created with the M code above
- Add a new custom column and simply reference the Date query. Press ok.
- 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...
- all of the dates
- 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.
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:
- This is the Date query created with the M code above
- Add a new custom column and simply reference the Date query. Press ok.
- 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...
- all of the dates
- 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.