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
371 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
257 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
289 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
329 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
311 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.

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