Power query

[Anonymous]

Need an output of each invoices with their shipper name and invoice value in power query

[ronrsnfld]

Considering the lower portion of your report to be the relevant table

You can use this code pasted into the Advanced Editor

Please read the code comments for clarifications and explanation of the algorithm:

let

//Select the part of the invoice that represents the company entries
// change name accordingly in the next step
    Source = Excel.CurrentWorkbook(){[Name="AR_Ledger"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
            {"Customer No./Name /#(lf) Document Number/Type", type text}, 
            {"Cus. Code /#(lf) Doc. Type", type any}, 
            {"Balance", Currency.Type}}),

//Group using the null values in the Balance column and the fourth and fifth arguments of the Table.Group function
//  first row will have the Customer No./Name
//  last row should have the total of the items

// But American Eagle has wrong total, possibly due to rounding error in your sample
//If you need to check for that, change the algorithm for Invoice Balance to sum all
//   except the last line
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Balance"}, {
        {"Customer No./Name", each [#"Customer No./Name /#(lf) Document Number/Type"]{0}, type text},
        {"Invoice Balance", each List.Last([Balance]), Currency.Type}
        }, GroupKind.Local,(x,y)=> Number.From(x[Balance]=null and y[Balance]=null)),
    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Balance"})
in
    #"Removed Columns"

To obtain this result:

[pcoley]

1st create a custom column:  if the column11(K) is null then take the value of the column1(A). 

2nd select the created custom column and fill down

3rd filter the column2(B) keeping only the cells needed: as seen you must filter the cells with "IN" value.

Rename the columns as needed:

Column1(A)--> DocNumber;

Column2(B)-->TypeOfDoc;

Custom-->Amount

I hope this helps, if so, please mark as solution. Kudos are welcome

.