Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 even though the columns line up in the PDFs, for whatever reason, in the output in Power Query, some of the PDFs have columns with null values. It seems like it would be easy to fix if I could just shift data left to null cells so that all the data lines up. Is there a way to replicate the sift cells left functionality in Power Query, or perhaps another approach to solve this issue?
Before -
Col 1 Col 2 Col 3 Col 4
1 null 1 1
1 1 1 null
1 1 1 null
After -
Col 1 Col 2 Col 3 Col 4
1 1 1 null
1 1 1 null
1 1 1 null
Thanks
For anyone out there looking for a way to do this with a few simple clicks, I was able to get all data shifted left by the following:
In Power Query;
@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"
Proud to be a Super User!
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 |
Hi,
Simply delete the columns with the heading of Date and Quantity before and rename the columns with no headings to Date and Quantity before.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |