Forum Discussion
How can I convert a table with duplicate IDs and string values in Power Query?
First, I need to duplicate each row when ID = ABC. Then, I need to divide each row into three and change the ID from ABC to 021, 026, 111, as these are the only IDs with strings. Do you have any ideas for how to accomplish this using Power Query?
| Region | Hours | Fruit | ID | Date |
| Austria | 12 | apple | ABC | 12.12.2022 |
| Austria | 10 | pineapple | ABC | 12.12.2023 |
How do I convert from this table to this one?
| Region | Hours | Fruit | ID | Date |
| Austria | 4 | apple | 021 | 12.12.2022 |
| Austria | 4 | apple | 026 | 12.12.2022 |
| Austria | 4 | apple | 111 | 12.12.2022 |
| China | 3.33 | pineapple | 021 | 12.12.2023 |
| China | 3.33 | pineapple | 026 | 12.12.2023 |
| China | 3.33 | pineapple | 111 | 12.12.2023 |
Hi Anonymous ,
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwtLinKTFRQ0lEyNAISiQUFOalA2tHJGSykB0RGBkZGSrE6qIoNgERBZl4qWIMCpg5jZB0gCVMgkZSYB4RAhourG5riWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Region " = _t, Hours = _t, Fruit = _t, ID = _t, Date = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Region ", type text}, {"Hours", Int64.Type}, {"Fruit", type text}, {"ID", type text}, {"Date", type date}}), //Relevant steps from here -------> addNewID = Table.AddColumn(chgTypes, "newID", each if [ID] = "ABC" then {"021", "026", "111"} else {[ID]}), addNewHours = Table.AddColumn(addNewID, "newHours", each [Hours] / List.Count([newID])), expandNewID = Table.ExpandListColumn(addNewHours, "newID") in expandNewIDTo get this output:
I've assumed that you want to keep the existing [ID] if it's not "ABC".
Pete
1 Reply
Hi Anonymous ,
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwtLinKTFRQ0lEyNAISiQUFOalA2tHJGSykB0RGBkZGSrE6qIoNgERBZl4qWIMCpg5jZB0gCVMgkZSYB4RAhourG5riWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Region " = _t, Hours = _t, Fruit = _t, ID = _t, Date = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Region ", type text}, {"Hours", Int64.Type}, {"Fruit", type text}, {"ID", type text}, {"Date", type date}}), //Relevant steps from here -------> addNewID = Table.AddColumn(chgTypes, "newID", each if [ID] = "ABC" then {"021", "026", "111"} else {[ID]}), addNewHours = Table.AddColumn(addNewID, "newHours", each [Hours] / List.Count([newID])), expandNewID = Table.ExpandListColumn(addNewHours, "newID") in expandNewIDTo get this output:
I've assumed that you want to keep the existing [ID] if it's not "ABC".
Pete