Forum Discussion
Shift data left to null cell
I'm wondering if it is possible to replicate the Excel trick where you select all empty cells using crtl-g, delete values and shift cells left. I'm exporting and merging data from 100s of pdfs and ev...
Anonymous -
Possibly merge the columns (2,3,4), delimit by space, then trim, then split column by space?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiQzCO1YmGsiAYi0AsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Column2", type text}, {"Column3", type text}, {"Column4", type text}}, "en-US"),{"Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Merged", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", Int64.Type}})
in
#"Changed Type1"That works when the null spaces are at the start or end, but I'm actually trying to apply this solution to many columns at once ( I should have mentioned this) and the TRIM function does not seem to work for extra spaces between text.
Anonymous: I would like to know how to shift cells too. Did you ever come up with a solution?
Hi,
Share some data and show the expected result.
Ashish_Mathur: I need to pull this data weekly for reports, but the system shifts the column name when exporting it to a CSV file. Thank you for any tips you can give.
Before >>>
Part # Part Type <--Shift Left-- Date <--Shift Left-- Quantity Before 288 Adult 3 Ply Face Masks (disposable) Update 7/23/2020 8:27 0 97.95 288 Adult 3 Ply Face Masks (disposable) Update 7/23/2020 8:47 2156000 97.95 288 Adult 3 Ply Face Masks (disposable) Update 8/3/2020 16:21 2156000 0 288 Adult 3 Ply Face Masks (disposable) Update 8/3/2020 16:24 3565650 0 After >>>
Part # Part Type Date Quantity Before Quantity After 288 Adult 3 Ply Face Masks (disposable) Update 7/23/2020 8:27 0 97.95 288 Adult 3 Ply Face Masks (disposable) Update 7/23/2020 8:47 2156000 97.95 288 Adult 3 Ply Face Masks (disposable) Update 8/3/2020 16:21 2156000 0 288 Adult 3 Ply Face Masks (disposable) Update 8/3/2020 16:24 3565650 0
