Forum Discussion
COIL-ibesmond
Helper I
Helper IHow to merge null rows with a single value to non-null rows and create new columns for those values
Any ideas for how to solve this situation in Power Query?
I have a data set with 70+ columns. One column has data consuming multiple rows, however the number of rows is not consistent because the data is coming from a concatenated datasource that needs to be broken out into additional columns.
Here is some sample data.
| Date Started | ID Record | Location | Client ID | Product ID | Date Ended | Issue(s) |
| 12/1/2023 | 5062 | CA | 22016 | 950543 | 12/5/2023 | MB122751 |
| Shipped to wrong address | ||||||
| ; MB166876 | ||||||
| New product needed | ||||||
| 4/27/2023 | 5651 | CT | 23866 | 957282 | 4/28/2023 | MB170216 |
| Product not needed | ||||||
| 5/29/2023 | 5776 | UT | 27154 | 976631 | 6/5/2023 | MB129825 |
| Wrong item shipped | ||||||
| ; MB119711 | ||||||
| New product needed | ||||||
| 6/16/2023 | 5143 | LA | 26856 | 988202 | 6/17/2023 | MB152495 |
| Product defective | ||||||
| 5/11/2023 | 5462 | WA | 29612 | 949624 | 5/12/2023 | MB111923 |
| Product never shipped | ||||||
| 4/7/2023 | 5573 | TN | 28910 | 900559 | 4/8/2023 | MB147625 |
| Product defective | ||||||
| ; MB119718 | ||||||
| Ship new product | ||||||
| 6/19/2023 | 5032 | FL | 25040 | 942570 | 6/26/2023 | MB155569 |
| Incorrect Address | ||||||
| ; MB198598 | ||||||
| Shipped two products | ||||||
| 10/9/2023 | 5582 | WA | 23478 | 915807 | 10/13/2023 | MB158250 |
| Product refused on delivery | ||||||
| 2/16/2023 | 5519 | UT | 21579 | 973459 | 2/21/2023 | MB184858 |
| Shipped to wrong address | ||||||
| ; MB100392 | ||||||
| Missing product | ||||||
| 10/4/2023 | 5253 | CA | 23892 | 979444 | 10/11/2023 | MB108939 |
| Product defective | ||||||
| ; MB135906 | ||||||
| Ship new product | ||||||
| 8/29/2023 | 5974 | NC | 28001 | 922312 | 9/3/2023 | MB134361 |
| Product not needed | ||||||
| 11/9/2023 | 5768 | MI | 24227 | 932870 | 11/15/2023 | MB158967 |
| Shipped to wrong address | ||||||
| ; MB181152 | ||||||
| Wrong product shipped | ||||||
| 4/2/2023 | 5437 | VT | 21084 | 988177 | 4/5/2023 | MB166099 |
| Duplicate product shipped | ||||||
| 12/26/2022 | 5970 | NE | 23046 | 984105 | 12/31/2022 | MB128569 |
| Missing product | ||||||
| ; MB171239 | ||||||
| Product shipped separately |
This is the desired result.
| Date Started | ID Record | Location | Client ID | Product ID | Date Ended | Issue(s) | Reason | Issue ID | Reason 2 |
| 12/1/2023 | 5062 | CA | 22016 | 950543 | 12/5/2023 | MB122751 | Shipped to wrong address | MB166876 | New product needed |
| 4/27/2023 | 5651 | CT | 23866 | 957282 | 4/28/2023 | MB170216 | Product not needed | ||
| 5/29/2023 | 5776 | UT | 27154 | 976631 | 6/5/2023 | MB129825 | Wrong item shipped | MB119711 | New product needed |
| 6/16/2023 | 5143 | LA | 26856 | 988202 | 6/17/2023 | MB152495 | Product defective | ||
| 5/11/2023 | 5462 | WA | 29612 | 949624 | 5/12/2023 | MB111923 | Product never shipped | ||
| 4/7/2023 | 5573 | TN | 28910 | 900559 | 4/8/2023 | MB147625 | Product defective | MB119718 | Ship new product |
| 6/19/2023 | 5032 | FL | 25040 | 942570 | 6/26/2023 | MB155569 | Incorrect Address | MB198598 | Shipped two products |
| 10/9/2023 | 5582 | WA | 23478 | 915807 | 10/13/2023 | MB158250 | Product refused on delivery | ||
| 2/16/2023 | 5519 | UT | 21579 | 973459 | 2/21/2023 | MB184858 | Shipped to wrong address | MB100392 | Missing product |
| 10/4/2023 | 5253 | CA | 23892 | 979444 | 10/11/2023 | MB108939 | Product defective | MB135906 | Ship new product |
| 8/29/2023 | 5974 | NC | 28001 | 922312 | 9/3/2023 | MB134361 | Product not needed | ||
| 11/9/2023 | 5768 | MI | 24227 | 932870 | 11/15/2023 | MB158967 | Shipped to wrong address | MB181152 | Wrong product shipped |
| 4/2/2023 | 5437 | VT | 21084 | 988177 | 4/5/2023 | MB166099 | Duplicate product shipped | ||
| 12/26/2022 | 5970 | NE | 23046 | 984105 | 12/31/2022 | MB128569 | Missing product | MB171239 | Product shipped separately |
Thank you,
ibesmond
4 Replies
- AlienSx
Super User
let Source = your_table, f = (tbl) => [lst = List.Skip(tbl[#"Issue(s)"]), res = tbl{0} & [Reason = lst{0}?, Issue ID = Text.Remove(lst{1}?, {" ", ";"}), Reason 2 = lst{2}?] ][res], gr = Table.Group( Source, "ID Record", {{"row", f}}, GroupKind.Local, (s, c) => Byte.From(Value.Is(c, Number.Type)) ), z = Table.FromRecords(gr[row]) in z
COIL-ibesmondAlienSx For some reason it is only producing the first record. I have a bunch of columns with nulls in both rows. I pasted some more sample data that better reflects my dataset. Any ideas how I could modify your M code to produce the desired results?
ID Record IDATE TYPE Title Status Category Description LC2 LC3 LTAG ATVT SLO RNumber Time SV Date SB Time SB RB DE RFI FAR DTP R&R RA ACI DRP ERN ICLL NRDS ACT AAG CANS CCLS RSOU QRCD OAA OFAA TTCN PONO FEW SEIS INGRI CUMCO COQE LILH RIKLV RFRV RFCLU Issue(s) ETIVG ENTRV EPGCL ERCLD 31207-001 1/5/2023 ER PSB Review HID XXX XX XX B O BBP 25 12/31/1899 5:10:00 AM null null null MS 1/6/2023 null null null null null null null null null null null null null null null null null null null null null null null null Med UL Medium HT null 31207-002 null 7/25/2023 null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null CUFSABTP. SO. null null null null 30104-001 1/4/2023 I DS Closed I XXX XX XX T C TY 983 12/31/1899 3:40:00 PM AL 1/4/2023 12/31/1899 3:00:00 AM AL 1/5/2023 AL null VMUC Yes Yes CM 1/4/2023 BAPC623 RW 10 FFOH ISW null null null null null null null null null null No null null null null TP CA 30223-001 1/4/2023 1/4/2023 3/30/2023 3/30/2023 null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null VSC. null null null null 30105-001 1/5/2023 PRER OFO Closed I XXX XX XX null L YTO 227 12/31/1899 7:00:00 PM HB 1/5/2023 12/31/1899 4:00:00 AM HB 1/6/2023 HB null null No null null null null null null null null null FM TCTTR 20 10 10 null null null null null null null null null TP CA 30213-005 1/5/2023 1/5/2023 2/15/2023 2/15/2023 null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null OOT. SC. null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null ; A30228-001 null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null VWI. SC. null null null null 30105-002 1/6/2023 I SWLC Closed I XXX XX XX null W LEOB null 12/31/1899 11:10:00 AM MA null null BH 1/6/2023 BH null null No null null null null null null SLF SSR null null null null null null null null null null Yes null Med UL High RA CA 30106-001 1/6/2023 6/25/2023 7/7/2023 7/7/2023 null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null PSADTLCAN. SC. null null null null
- AlienSx
COIL-ibesmond try this
let Source = your_table, f = (tbl) => [lst = List.Skip(tbl[#"Issue(s)"]), res = tbl{0} & [Reason = lst{0}?, Issue ID = Text.Remove(lst{1}?, {" ", ";"}), Reason 2 = lst{2}?] ][res], gr = Table.Group( Source, "IDATE", {{"row", f}}, GroupKind.Local, (s, c) => Byte.From(Value.Is(c, DateTime.Type)) ), z = Table.FromRecords(gr[row]) in z - dufoq3
Community Champion
Hi COIL-ibesmond,
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVZda9tAEPwrws8B3e7d3gd9StMWAokpNG0eTB6CdWkNqWUkJ6H/vrunyD6Dc1YKfbAlSyvNzezsnBeLGWANNSrUs7MZKYt8uDjnL0QFlo+BFBm5yYU0Fl5/BERHMLs7W8wqvnDs8+3XarOJTbVtq5euXf+s7pumi31ffKj6UPHLrfXOFuvm8aXadG3ztNxW6xib2KRyU6PbsbG8QGZzI2y0twMbh144cqHP2DiFUAb8OoK1B4AsSdgBOicY3xOgAzIC6KzVsgx7qF7wSEW82yTZaht/V/0g5ATdIDgoN+UN3WwNdkcDUr+vkgusp6Sb93w30QCX8SA0ocxj1K2JD3G5XT3HV9lg7zqTXHeb8IIF+RFMsCj6cSFmeMyQTyf1KT7H7kA6U++tQU4ON3OB9AGUQCpFFJI1cmcYZ0906jjD043yJ6eHSey6NbZp7zalRakvV8KBlEkcDJJTqU1o8zYR2VCEu1wv267j9Vfnk6c0eAqnSaQIeGlHHsOLQdV7IuT3/dfGeSEC5JXjEy4EnTPhuVGT2tHFh6eesds1d+aR29L9Sc9hbnWCsJtYIBfSxGqTfMC+gwzZG08T2b4/8JTSAYt116u+X/E7czuwOGZHBUkOQ3ZrH9IUuWCMeVUx56J80GU//IupNQVVDtGjpvZ5hAYn651fpMFUSpIzIOohFOrcCdpoW866NyKblcgi24rdri8Fz/CeJjAafRoiLgQ68F6w7n85wAOH6YQdYQzvw2TDfZhq4fBjcLTyaQ/yHpxcNQd7kLUqlE3w6WnzuFreb+NRVAnmNEk4NE4km39O9lNm2DQMKJqlvw4axkrZ/fypODrm9rJ+DnCipV85VH3c3HdM7pFz4e4v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Started" = _t, #"ID Record" = _t, Location = _t, #"Client ID" = _t, #"Product ID" = _t, #"Date Ended" = _t, #"Issue(s)" = _t]), ReplaceBlankWithNull = Table.ReplaceValue(Source, null, null, (x,y,z)=> if Text.Trim(x) = "" then null else x, Table.ColumnNames(Source)), FilledDown = Table.FillDown(ReplaceBlankWithNull,Table.ColumnNames(Source)), TrimmedIssues = Table.TransformColumns(FilledDown,{{"Issue(s)", each Text.Trim(_, {";", " "}), type text}}), GroupedRows = Table.Group(TrimmedIssues, {"Date Started", "ID Record", "Location", "Client ID", "Product ID", "Date Ended"}, {{"Combine", each Text.Combine([#"Issue(s)"], ";")}}), SplitColumnByDelimiter = Table.SplitColumn(GroupedRows, "Combine", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Issues(s)", "Reason", "Issue ID", "Reason 2"}) in SplitColumnByDelimiter
