Forum Discussion
Adding multiple entries for each record in a sumamrized table
Hello all, following many failed attempts and hours searching for what I think must be super simple I am missing the beat here.
I am creating a summary dimensions table.
I have a list of UserIDs that I summarise. In the example below I have a summary of 5 user IDs.
| UserID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
I also want to be able to add 3 records for each User ID:
"Learning community"
"Feedback"
"Engagement"
My desired output:
| 1 | Learning community |
| 2 | Learning community |
| 3 | Learning community |
| 4 | Learning community |
| 5 | Learning community |
| 1 | Feedback |
| 2 | Feedback |
| 3 | Feedback |
| 4 | Feedback |
| 5 | Feedback |
| 1 | Engagement |
| 2 | Engagement |
| 3 | Engagement |
| 4 | Engagement |
| 5 | Engagement |
My formula so far only enables me to add each question for an individual column:
Test_Question_UserID =
SUMMARIZE('_Source Data_Course',
'_Source Data_Course'[UserID],
"Question","Learning community")It is easier to do this in the Query Editor. Please paste this in a blank query (Advanced Editor, replace the existing text with this)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {"Learning Community", "Feedback", "Engagement"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type text}})
in
#"Changed Type1"It adds a List with your three values to each of the rows and then expands to new rows to get your desired result.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thanks so much mahoneypat such a nice solution in m.
Greatly appreciated
2 Replies
- mahoneypat
Microsoft Employee
It is easier to do this in the Query Editor. Please paste this in a blank query (Advanced Editor, replace the existing text with this)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {"Learning Community", "Feedback", "Engagement"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type text}})
in
#"Changed Type1"It adds a List with your three values to each of the rows and then expands to new rows to get your desired result.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thanks so much mahoneypat such a nice solution in m.
Greatly appreciated