Forum Discussion
Sharepoint choice column with multiple values imports as multiple rows
Hi,
I have a dataset that is connected to sharepoint list and a couple of the columns are multiple choices.
Some of the fields have more than 1 choice. In my real dataset I have a field that can store multiple email addresses and the other stores multiple user-defined values.
When I put this into sharepoint, I am getting two rows. The data looks something like this:
| ID | Name | Owner | Capex | Opex |
| 298 | Bob | Fred | 100000 | 0 |
| 298 | Bob | Jim | 100000 | 0 |
What I want is:
| ID | Name | Owner | 100000 | 0 |
| 298 | Bob | Fred, Jim | 100000 | 0 |
My skill level is lower intermediate so hopefully the answer is not hugely complicated.
Thanks.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrK0UNJRcspPApJuRakpQMrQAASADAOlWB1UFV6ZuWgKYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Owner = _t, Capex = _t, Opex = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Owner", type text}, {"Capex", Int64.Type}, {"Opex", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Name"}, {{"Owner", each Text.Combine([Owner],", "), type nullable text}, {"Capex", each List.Min([Capex]), type nullable number}, {"Opex", each List.Min([Opex]), type nullable number}}) in #"Grouped Rows"
2 Replies
- Vijay_A_Verma
Most Valuable Professional
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrK0UNJRcspPApJuRakpQMrQAASADAOlWB1UFV6ZuWgKYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Owner = _t, Capex = _t, Opex = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Owner", type text}, {"Capex", Int64.Type}, {"Opex", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Name"}, {{"Owner", each Text.Combine([Owner],", "), type nullable text}, {"Capex", each List.Min([Capex]), type nullable number}, {"Opex", each List.Min([Opex]), type nullable number}}) in #"Grouped Rows" - Vijay_A_Verma
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrK0UNJRcspPApJuRakpQMrQAASADAOlWB1UFV6ZuWgKYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Owner = _t, Capex = _t, Opex = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Owner", type text}, {"Capex", Int64.Type}, {"Opex", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Name"}, {{"Owner", each Text.Combine([Owner],", "), type nullable text}, {"Capex", each List.Min([Capex]), type nullable number}, {"Opex", each List.Min([Opex]), type nullable number}}) in #"Grouped Rows"