Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
COIL-ibesmond
Helper I
Helper I

How to merge null rows with a single value to non-null rows and create new columns for those values

‎02-07-2024 06:25 PM

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 StartedID RecordLocationClient IDProduct IDDate EndedIssue(s)
12/1/20235062CA2201695054312/5/2023MB122751
      Shipped to wrong address
       ; MB166876
      New product needed
4/27/20235651CT238669572824/28/2023MB170216
      Product not needed
5/29/20235776UT271549766316/5/2023MB129825
      Wrong item shipped
       ; MB119711
      New product needed
6/16/20235143LA268569882026/17/2023MB152495
      Product defective
5/11/20235462WA296129496245/12/2023MB111923
      Product never shipped
4/7/20235573TN289109005594/8/2023MB147625
      Product defective
       ; MB119718
      Ship new product
6/19/20235032FL250409425706/26/2023MB155569
      Incorrect Address
       ; MB198598
      Shipped two products
10/9/20235582WA2347891580710/13/2023MB158250
      Product refused on delivery
2/16/20235519UT215799734592/21/2023MB184858
      Shipped to wrong address
       ; MB100392
      Missing product
10/4/20235253CA2389297944410/11/2023MB108939
      Product defective
       ; MB135906
      Ship new product
8/29/20235974NC280019223129/3/2023MB134361
      Product not needed
11/9/20235768MI2422793287011/15/2023MB158967
      Shipped to wrong address
       ; MB181152
      Wrong product shipped
4/2/20235437VT210849881774/5/2023MB166099
      Duplicate product shipped
12/26/20225970NE2304698410512/31/2022MB128569
      Missing product
       ; MB171239
      Product shipped separately

 

This is the desired result.

 

Date StartedID RecordLocationClient IDProduct IDDate EndedIssue(s)ReasonIssue IDReason 2
12/1/20235062CA2201695054312/5/2023MB122751Shipped to wrong addressMB166876New product needed
4/27/20235651CT238669572824/28/2023MB170216Product not needed  
5/29/20235776UT271549766316/5/2023MB129825Wrong item shippedMB119711New product needed
6/16/20235143LA268569882026/17/2023MB152495Product defective  
5/11/20235462WA296129496245/12/2023MB111923Product never shipped  
4/7/20235573TN289109005594/8/2023MB147625Product defectiveMB119718Ship new product
6/19/20235032FL250409425706/26/2023MB155569Incorrect AddressMB198598Shipped two products
10/9/20235582WA2347891580710/13/2023MB158250Product refused on delivery 
2/16/20235519UT215799734592/21/2023MB184858Shipped to wrong addressMB100392Missing product
10/4/20235253CA2389297944410/11/2023MB108939Product defectiveMB135906Ship new product
8/29/20235974NC280019223129/3/2023MB134361Product not needed  
11/9/20235768MI2422793287011/15/2023MB158967Shipped to wrong addressMB181152Wrong product shipped
4/2/20235437VT210849881774/5/2023MB166099Duplicate product shipped  
12/26/20225970NE2304698410512/31/2022MB128569Missing productMB171239Product shipped separately

 

Thank you,

 

ibesmond

Labels:
Message 1 of 5
373 Views
0
4 REPLIES 4
dufoq3
Super User
Super User

‎02-11-2024 07:33 AM

Hi @COIL-ibesmond,

Result:

dufoq3_0-1707665580554.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 5 of 5
259 Views
0
AlienSx
Super User
Super User

‎02-08-2024 10:25 PM

@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
Message 4 of 5
291 Views
0
AlienSx
Super User
Super User

‎02-07-2024 09:57 PM 
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
Message 2 of 5
331 Views
0
COIL-ibesmond
Helper I
Helper I
In response to AlienSx

‎02-08-2024 08:47 AM

@AlienSx 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 RecordIDATETYPETitleStatusCategoryDescriptionLC2LC3LTAGATVTSLORNumberTimeSVDate SBTime SBRBDERFIFARDTPR&RRAACIDRPERNICLLNRDSACTAAGCANSCCLSRSOUQRCDOAAOFAATTCNPONOFEWSEISINGRICUMCOCOQELILHRIKLVRFRVRFCLUIssue(s)ETIVGENTRVEPGCLERCLD
31207-0011/5/2023ERPSBReviewHIDXXXXXXXBOBBP2512/31/1899 5:10:00 AMnullnullnullMS1/6/2023nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullMedULMediumHTnull31207-002null7/25/2023nullnull
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullCUFSABTP. SO.nullnullnullnull
30104-0011/4/2023IDSClosedIXXXXXXXTCTY98312/31/1899 3:40:00 PMAL1/4/202312/31/1899 3:00:00 AMAL1/5/2023ALnullVMUCYesYesCM1/4/2023BAPC623RW10FFOHISWnullnullnullnullnullnullnullnullnullnullNonullnullnullnullTPCA30223-0011/4/20231/4/20233/30/20233/30/2023
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullVSC.nullnullnullnull
30105-0011/5/2023PREROFOClosedIXXXXXXXnullLYTO22712/31/1899 7:00:00 PMHB1/5/202312/31/1899 4:00:00 AMHB1/6/2023HBnullnullNonullnullnullnullnullnullnullnullnullFMTCTTR201010nullnullnullnullnullnullnullnullnullTPCA30213-0051/5/20231/5/20232/15/20232/15/2023
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullOOT. SC.nullnullnullnull
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull; A30228-001nullnullnullnull
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullVWI. SC.nullnullnullnull
30105-0021/6/2023ISWLCClosedIXXXXXXXnullWLEOBnull12/31/1899 11:10:00 AMMAnullnullBH1/6/2023BHnullnullNonullnullnullnullnullnullSLFSSRnullnullnullnullnullnullnullnullnullnullYesnullMedULHighRACA30106-0011/6/20236/25/20237/7/20237/7/2023
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullPSADTLCAN. SC.nullnullnullnull
Message 3 of 5
313 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All