Power Query M语言拆分列报错及发票数据处理需求求助

Corrected M Code #

let
    Source = #"PO data",
    
    // Keep only required columns
    #"Removed Other Columns" = Table.SelectColumns(Source, {"Order", "Invoice(paid amount:paid date)"}),
    
    // Rename invoice column
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns", {{"Invoice(paid amount:paid date)", "Related_Invoices"}}),
    
    // Function to parse invoice details with error handling
    ExtractInvoiceDetails = (text as text) as list =>
        let
            // Split entries by "): " and clean trailing parentheses
            InvoiceList = Text.Split(text, "): "),
            InvoiceListCleaned = List.Transform(InvoiceList, each Text.TrimEnd(_, ")")),
            // Parse each entry with fallback for invalid formats
            InvoiceDetails = List.Transform(InvoiceListCleaned, (entry) =>
                try
                    let
                        Parts = Text.Split(entry, " ("),
                        InvoiceNumber = Parts{0},
                        AmountDate = Text.Split(Parts{1}, ":"),
                        CurrencyAmount = Text.Split(AmountDate{0}, " "),
                        Currency = CurrencyAmount{0},
                        Amount = CurrencyAmount{1},
                        Date = AmountDate{1}
                    in
                        [InvoiceNumber=InvoiceNumber, Currency=Currency, Amount=Amount, Date=Date]
                otherwise
                    [InvoiceNumber=entry, Currency=null, Amount=null, Date=null]
            )
        in
            InvoiceDetails,
    
    // Add invoice details list (retain orders with no invoices)
    #"Added Invoice Details" = Table.AddColumn(#"Renamed Columns", "InvoiceDetails", 
        each 
            let
                cleanedText = if [Related_Invoices] is null then "" else Text.Trim(Text.From([Related_Invoices]))
            in
                if cleanedText = ""
                then {[InvoiceNumber=null, Currency=null, Amount=null, Date=null]}
                else ExtractInvoiceDetails(cleanedText)
    ),
    
    // Expand list into rows
    #"Expanded Invoice Details" = Table.ExpandListColumn(#"Added Invoice Details", "InvoiceDetails"),
    
    // Expand record into individual columns
    #"Expanded Record Columns" = Table.ExpandRecordColumn(#"Expanded Invoice Details", "InvoiceDetails", 
        {"InvoiceNumber", "Currency", "Amount", "Date"}, 
        {"InvoiceNumber", "Currency", "Amount", "Date"}
    ),
    
    // Convert转换 columnsGrandJoy-links鼎¬man Along_sequence按Game
 for user>
    #"Changed Type" = Table.TransformColumns(#"Expanded Record Columns", {
        {"Amount", each try Number.From(_) otherwise null, type number},
        {"Date", each try Date.From(_) otherwise null, type date}
    })
in
    #"Changed Type"

Key Fixes & Improvements #

1. Null/Empty Value Handling:

   • Added logic to retain orders with no invoices by returning a list containing a null record instead of an empty list, ensuring no orders are dropped during expansion.
   • Cleaned input text to handle whitespace and nulls before parsing.

2. Error Resilience:

   • Wrapped parsing logic in try...otherwise to handle invalid invoice formats gracefully, avoiding full process failures and preserving raw entry data as a fallback.

3. Data Formatting:

   • Converted Amount to numeric type and Date to date type for proper data analysis and sorting.
   • Trimmed whitespace from input text to avoid parsing issues.

4. Complete Column Expansion:

   • Expanded parsed record data into separate, named columns (InvoiceNumber, Currency, Amount, Date) to meet your requirement of splitting invoice details into distinct fields.

This code will process all orders, handle edge cases like nulls or invalid formats, and produce a clean, structured table with all required invoice details.

内容的提问来源于stack exchange，提问作者Amir