When I want to split a column to several distinct columns by using the M language I got the below error message:
Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]
The error occur here:
= Table.ExpandListColumn(#”Added Invoice Details”, “InvoiceDetails”)

I’m seeking help with writing an M code for Power Query to handle invoice data within my table. Here’s a breakdown of what I need:

Background:
I have a table in Power Query with 42 columns. I’m interested in extracting data from two specific columns:

1. “Order”: This column contains the order number.
2. “Invoice(paid amount:paid date)”: This column contains related invoices to each order, formatted as follows:
   • Example: “34539 (GBP 2163.17:2022-11-23): 34540 (GBP 3588.77:2022-11-23): 34553 (GBP 17106.3:2022-11-23): 34554 (GBP 59846.12:2022-11-23): 34559 (GBP 39.46:2022-11-23)”
   • Each invoice entry consists of the invoice number, currency code, invoice amount, and invoice date, separated by “: “, with each entry separated by “: “.
   • The related invoices for each order can range up to 150 items.

Requirements:

1. Rename the column “Invoice(paid amount:paid date)” to “Related_Invoices”.
2. Split the “Related_Invoices” column into separate columns for invoice number, currency code, invoice amount, and invoice date, and name them as “Invoice Number 1”, “Invoice Number 2”, and so on.
3. Remove any extra characters and format the data appropriately.
4. Generate a table listing all orders with related invoice details (invoice number, currency code, invoice amount, and invoice date) in front of each row.

I would greatly appreciate any assistance or guidance on writing the M code to accomplish these tasks efficiently.

Thank you in advance for your help!

My code is :

let
    // Source table
    Source = #"PO data",
    
    // Select only the required columns
    #"Removed Other Columns" = Table.SelectColumns(Source, {"Order", "Invoice(paid amount:paid date)"}),
    
    // Rename the "Invoice(paid amount:paid date)" column to "Related_Invoices"
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns", {{"Invoice(paid amount:paid date)", "Related_Invoices"}}),
    
    // Function to split and extract details from Related_Invoices column
    ExtractInvoiceDetails = (text as text) as list =>
        let
            InvoiceList = Text.Split(text, "): "),
            InvoiceListCleaned = List.Transform(InvoiceList, each Text.TrimEnd(_, ")")),
            InvoiceDetails = List.Transform(InvoiceListCleaned, each 
                let
                    Parts = Text.Split(_, " ("),
                    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]
            )
        in
            InvoiceDetails,
    
    // Add columns with details extracted from Related_Invoices
    #"Added Invoice Details" = Table.AddColumn(#"Renamed Columns", "InvoiceDetails", each ExtractInvoiceDetails(Text.From([Related_Invoices]))),
    
    // Expand the list of invoice details into rows
    #"Expanded Invoice Details" = Table.ExpandListColumn(#"Added Invoice Details", "InvoiceDetails")
in
    #"Expanded Invoice Details"