Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I gave up trying to use nested tables. Way too complex for a newbie so ditched trying to use XML format.
Made fantastic progress today but am now properly stuck.
I've merged three columns using ; as a delimiter but the null cells cause the delimiter to be used where it is not required.
Tried many ways to remove them to no avail.
Before Merge!
After Merge!
I only want to remove ; ; after singular column Arts & Crafts, Technology & Gadgets, Health & Beauty and so on (NB they are a legacy of the three columns being merged into one) and the singular semi-colon after Technology & Gadgets on Row 11 (this is a legacy of three columns being merged into one).
I noticed the lack of null figures in the 2nd Column pre-merge. Perhaps this is a clue but I wouldn't have a clue how to decipher it.
Syntax required is:-
Row 1 Arts & Crafts
Row 5 Home & Living, Technology & Gadgets, Toys & Entertainment
Row 6 Technology & Gadgets
Row 8 Health & Beauty
Row 11 Home; Technology & Gadgets
Here is my example source code:-
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwqKVaIKTUwMDJTcC5KTCsptlawVorVobaMR35uKkzGJ7MsMy/dWiEkNTkjLz8nP70SJuOemJKeCtIUkl8JN8g1ryS1qCQxMy83Na8EbBhOjUTIeqQm5pRkwGScUhNLSyoJuJ2AgUCf4fGLUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Categories = _t])
Here is my code for the last two steps:-
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value3",{"Categories.1.1", "Categories.3"}),
#"Merged Columns1" = Table.CombineColumns(#"Removed Columns",{"Categories.1.2", "Categories.2.1", "Categories.2.2"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Categories")
Thanks in advance. I feel I've come a long way in 3 - 4 days but still stacks to learn.
Solved! Go to Solution.
= Table.TransformColumns(#"Merged Columns1",{{"Categories", each Text.TrimEnd(_, ";"), type text}})
Merge with ";" only instead of "; "
Text.TrimEnd([Column], ";")
After that replace ";" with "; " if you wanted the extra space after the semicolon.
Thanks.
Looks good but where do I put it please?
= Table.TransformColumns(#"Merged Columns1",{{"Categories", Text.TrimEnd([Column], ";"), type text}})
= Table.TransformColumns(#"Merged Columns1",{{"Categories", each Text.TrimEnd(_, ";"), type text}})
OK. Back to where I was and looking good apart from I cannot find where I entered "; " originally so I still have ";" at the end of Arts & Crafts lines only
EDIT!
Found it! It was way, way back in the steps.
I need to learn how to clean the code. I have far too many steps.
Many thanks for helping me with this, this evening.
I am very tired so need to go to bed else I will make more mistakes then wake up fresh.
Thanks again.
Sadly, I think I've messed it all up as I tried several things instead of waiting for a reply.
I need to try and work out where I was...