Forum Discussion
Expanding range stated in same cell.
Costan08 Perhaps the attached file is what you want. It allows for the five code structures that I mentioned earlier. It seems a rather clumsy approach, though.
The combined tables from the PDF contained 2238 line-items. The query produces 14648 line-items (216 more than in my previous attempt). Difficult to be sure that this one is complete now. Check it thoroughly please!
Costan08 Try sharing the file on OneDrive or similar. Otherwise, click on my "name tag" and send it to me via private message.
Power Query take a bit of an effort to learn, but once you do, you "can't live without it".
Costan08 Aha! Alpha-numeric is a different thing. Am attaching the PDF you sent to me. Don't have time right now to look into it. Perhaps someone else.
Costan08 Please find a partial PQ solution. I call it partial because I didn't manage to get the alpha-numeric ranges to perfection. Although the number sequences are in place I couldn't think of an easy way to re-insert the characters. You'll find one column containing the original range followed by one which just numbers but in a correct sequence.
See if this is something you can live with. If not, someone else has to step in and fix it. Or perhaps think of a better way all-together.
Your solution taught me a lot. I appreciate it. To resolve the very last bit of house cleaning (re-inserting the characters) I offer this:
DG_ListDiff = Table.AddColumn(#"Expanded Custom.2", "List Difference", each List.Difference(Text.ToList([#"Range colori.1"]),Text.ToList(Text.From([#"Custom - Copy"])))), DG2_ExtractList = Table.TransformColumns(DG_ListDiff, {"List Difference", each Text.Combine(List.Transform(_, Text.From)), type text}), DG3_PositionOf = Table.AddColumn(DG2_ExtractList, "PositionOf", each List.PositionOfAny(Text.ToList([#"Range colori.1"]),Text.ToList([List Difference]))), DG4_ListInsert = Table.AddColumn(DG3_PositionOf, "Rebuild Alpha", each List.InsertRange(Text.ToList(Text.From([#"Custom.2"])),[PositionOf],Text.ToList([List Difference]))), DG5_RebuildAlpha = Table.TransformColumns(DG4_ListInsert, {"Rebuild Alpha", each Text.Combine(List.Transform(_, Text.From)), type text}), #"Removed Columns3" = Table.RemoveColumns(DG5_RebuildAlpha,{"Range colori.2", "Custom", "Custom.1", "Range colori.1", "List Difference", "PositionOf","Custom.2","Custom - Copy"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"Rebuild Alpha", "Colori"}}),The end result (refer to "Rebuild Alpha" vs "Range colori" to see the text characters have been inserted into the numerical sequence. This requires contiguous text characters when there are multiple - I looked briefly and didn't notice non-contiguous text characters - but admittedly did not do a formal validation to ensure such.
DexterG_III Nice, but unfortunately, the color codes do have non-continues character sequences. I noticed 5 different alpha-numeric structures:
n, nCn, Cn, CnC and CnCn where n stands for a number (or number sequence) and C stands for a character (or character sequence).
So, you will find codes like A90000 and AA229A just to give an example.
