How can I convert a table with duplicate IDs and string values in Power Query?

[Anonymous]

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

[BA_Pete · Accepted solution]

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
    expandNewID

To get this output:

I've assumed that you want to keep the existing [ID] if it's not "ABC".

Pete