Forum Discussion
Continued ContributorSplitting the duration into column names
This is the data:
The final goal is to get the report with the following aggregation. How to do it either in DAX or Power Query?
- Try this M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMDQwswZWSgFKsTreSELm5oCRYHqTeCCaCpN0JRb6EUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, Start_date = _t, End_date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Type", Int64.Type}, {"Start_date", Int64.Type}, {"End_date", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each List.Numbers([Start_date], [End_date]-[Start_date]+1,1)),
#"Expanded Years" = Table.ExpandListColumn(#"Added Custom", "Years"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Years",{{"Years", Int64.Type}})
in
#"Changed Type1"
Paste into a Blank Query in Power Query to see what it is doing. Essentially a GenerateSeries but in M. Thanks to the answer from AllisonKennedy I was able to obtain what I wanted.
First, I used this Power Query M code:
let Source = Excel.Workbook(File.Contents("C:\Temp\PBI_example10.2.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Type", type text}, {"Start_date", Int64.Type}, {"End_date", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each List.Numbers([Start_date], [End_date]-[Start_date]+1,1)), #"Expanded Years" = Table.ExpandListColumn(#"Added Custom", "Years"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Years",{{"Years", Int64.Type}}) in #"Changed Type1"Then used Matrix visualization:
Now the result looks exactly like desired.
8 Replies
- AllisonKennedy
Community Champion
From looking at your other query, it seems like you just combined start, end into one column? Does the data ever span more than 2 years? So three years for example? How close is this sample data to what you actually need?
Zyg_D
AllisonKennedy wrote:From looking at your other query, it seems like you just combined start, end into one column? Does the data ever span more than 2 years? So three years for example? How close is this sample data to what you actually need?
Yes, it can span more than 3 years. In the question that you are referring to, I was using this DAX expression:
CONCATENATEX( GENERATESERIES(Table1[Start_date],Table1[End_date], 1), [Value],",")This sample is very close to the original data.
- AllisonKennedyTry this M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMDQwswZWSgFKsTreSELm5oCRYHqTeCCaCpN0JRb6EUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, Start_date = _t, End_date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Type", Int64.Type}, {"Start_date", Int64.Type}, {"End_date", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each List.Numbers([Start_date], [End_date]-[Start_date]+1,1)),
#"Expanded Years" = Table.ExpandListColumn(#"Added Custom", "Years"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Years",{{"Years", Int64.Type}})
in
#"Changed Type1"
Paste into a Blank Query in Power Query to see what it is doing. Essentially a GenerateSeries but in M.
- Zyg_D
Thanks to the answer from AllisonKennedy I was able to obtain what I wanted.
First, I used this Power Query M code:
let Source = Excel.Workbook(File.Contents("C:\Temp\PBI_example10.2.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Type", type text}, {"Start_date", Int64.Type}, {"End_date", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each List.Numbers([Start_date], [End_date]-[Start_date]+1,1)), #"Expanded Years" = Table.ExpandListColumn(#"Added Custom", "Years"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Years",{{"Years", Int64.Type}}) in #"Changed Type1"Then used Matrix visualization:
Now the result looks exactly like desired.
- Greg_Deckler
Zyg_D - Well, you could unpivot our start/end date columns. You also could use a separate Year table and use something like MC Aggregations. https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129
- Zyg_D
Greg_Deckler wrote:Zyg_D - Well, you could unpivot our start/end date columns. You also could use a separate Year table and use something like MC Aggregations. https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129
Hello, Greg. Thank you for the suggestion, I am trying to understand it. One question:
Will the unpivoting work if some values are not in the table? E.g.: Start_date is 2017, End_date is 2019. In this case 2018 is in the period, but it is not explicitly in the table, so as far as I understand, unpivoting would not be useful in this scenario?...
