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!
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.
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!
Riny_van_Eekelen I unfortunately closed that workbook without option to recover and need to sleep soon, but I do believe it's possible to
- Add two more columns directly after line 3 of the code above and use Text.Range in combination with the value from DG3_PositionOf to parse the remaining portion (after the first text character(s) were recognized) of both Range colori.1 & Custom - Copy .
- Add another column & use List Difference comparing the two new columns to return the second set of non contiguous characters
- Insert another column to return the position of the 2nd set of non-contiguous characters and add that to the original DG3_PositionOf value
- After the DG5RebuildAplpha step, use the same logic to insert the 2nd set of non-contiguous characters.
I admit, it's not clean and there's likely a much more elegant solution but I do believe that would work in a pinch.
Sorry I'm not able to provide the actual code now.
I'll check in tomorrow and provide if this thread remains unresolved by then.
DexterG_III Not as far I could discover.
Riny_van_Eekelen So all this time my hoping the data would be simple enough to support my formula has been a failed tactic. Reality sucks. lol.
Sincerely though, thanks for confirming. Is there a max number of non-contiguous possibilities? Is it possible to have CnCnC or more?
Dexter
- I was trying to re-insert the numbers by splitting columns an re-combining them based off the knowledge I have about how the codes are structured. This is a tedious and error prone process though. Again, though, I am a power query novice so I am unsure if there is a better process to achieve the same result.
