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!
I have never used power query nor the microsoft techcommunity.
Therefore it's difficult for me to understand how you achieved the result you posted, which is the exact answer I am looking for. However, I cannot seem to post neither pictures or the list I am trying to parse.
Could you please explain the process you went through?
Thank you
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".
- I have tried to reverse-engineer your power query, however I cannot seem to automatically fill the values in between the range extremes. I am not sure if this has to do to the fact that some of the ranges include alphanumeric values. Anyhow, I have sent you a copy of the PDF list I am trying to expand.
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.
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.
