Forum Discussion

CoffeeCup1's avatar
CoffeeCup1
New Member
2 years ago
Solved

Move cell to new column in row below and delete row

Hello, I am new to PowerQuery; apologies if this is a simple quesiton.   I have rows of "invoice" data. The name of the vendor appears above every entry in its own row. Also, there is a "total" row...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    If your data is really formed as you show, this code will produce your shown results:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Invoice Number", type text}, {"Invoice Date", type date}, {"Post Month", type date}, 
        {"Invoice Status", type text}, {"Payment/Check #", type text}, {"Due Date", type date}, 
        {"Amount", Currency.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 3), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Integer-Division"}, {
        {"all", (t)=>
        Table.FillUp(
        Table.AddColumn(t,"Vendor",each Splitter.SplitTextByDelimiter(": ")(t[Invoice Number]{0}){1}),
            List.RemoveItems(Table.ColumnNames(t),{"Payment/Check #"})){1}}
        }),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Integer-Division"}),
    #"Expanded all" = Table.ExpandRecordColumn(#"Removed Columns1", "all", {"Vendor","Invoice Number", "Invoice Date", "Post Month", "Invoice Status", "Payment/Check #", "Due Date", "Amount"}),
    #"Transform Post Month" = Table.TransformColumns(#"Expanded all",{"Post Month", each Date.ToText(_,"M/yyyy")}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Transform Post Month",{
        {"Vendor", type text}, {"Invoice Number", type text}, {"Invoice Date", type date}, 
        {"Invoice Status", type text}, {"Payment/Check #", type any}, 
        {"Due Date", type date}, {"Amount", Currency.Type}})
in
    #"Changed Type1"

     

    Results from your posted data:

     

     

     

spinfuzer's avatar
spinfuzer
Icon for Solution Sage rankSolution Sage
2 years ago

Add Custom Column

if Text.StartsWith([Invoice Number], "Vendor: ") ?? false then Text.AfterDelimiter([Invoice Number], ": ") else null

 

The ?? gives you null instead of error when invoice number is null.

 

Then fill down on the new custom column and filter invoice status for not equal to null (or some other column that has few values that cannot be null).

 

let
    Source = Binary.FromText("W3siSW52b2ljZSBOdW1iZXIiOiJWZW5kb3I6IENvY2EgQ29sYSIsIkludm9pY2UgRGF0ZSI6bnVsbCwiUG9zdCBNb250aCI6bnVsbCwiSW52b2ljZSBTdGF0dXMiOm51bGwsIlBheW1lbnRcL0NoZWNrICMiOm51bGwsIkR1ZSBEYXRlIjpudWxsLCJBbW91bnQiOm51bGx9LHsiSW52b2ljZSBOdW1iZXIiOiI0MDM5MjE1NzQyLTEzMjEyMyIsIkludm9pY2UgRGF0ZSI6IjIwMjMtMTEtMjFUMDA6MDA6MDAiLCJQb3N0IE1vbnRoIjoiMjAyMy0xMS0wMVQwMDowMDowMCIsIkludm9pY2UgU3RhdHVzIjoiUG9zdGVkIiwiUGF5bWVudFwvQ2hlY2sgIyI6bnVsbCwiRHVlIERhdGUiOiIyMDIzLTEyLTA2IiwiQW1vdW50Ijo4Mi4wMX0seyJJbnZvaWNlIE51bWJlciI6bnVsbCwiSW52b2ljZSBEYXRlIjpudWxsLCJQb3N0IE1vbnRoIjpudWxsLCJJbnZvaWNlIFN0YXR1cyI6bnVsbCwiUGF5bWVudFwvQ2hlY2sgIyI6IkNvY2EgQ29sYSBUb3RhbDoiLCJEdWUgRGF0ZSI6bnVsbCwiQW1vdW50Ijo4Mi4wMX0seyJJbnZvaWNlIE51bWJlciI6IlZlbmRvcjogQXBwbGUiLCJJbnZvaWNlIERhdGUiOm51bGwsIlBvc3QgTW9udGgiOm51bGwsIkludm9pY2UgU3RhdHVzIjpudWxsLCJQYXltZW50XC9DaGVjayAjIjpudWxsLCJEdWUgRGF0ZSI6bnVsbCwiQW1vdW50IjpudWxsfSx7Ikludm9pY2UgTnVtYmVyIjoiNzkzOTIxNTQ0Mi0xNTQ1MjMiLCJJbnZvaWNlIERhdGUiOiIyMDIzLTExLTA3VDAwOjAwOjAwIiwiUG9zdCBNb250aCI6IjIwMjMtMTEtMDFUMDA6MDA6MDAiLCJJbnZvaWNlIFN0YXR1cyI6IlBvc3RlZCIsIlBheW1lbnRcL0NoZWNrICMiOm51bGwsIkR1ZSBEYXRlIjoiMjAyMy0xMi0wNyIsIkFtb3VudCI6Mjg4MH0seyJJbnZvaWNlIE51bWJlciI6bnVsbCwiSW52b2ljZSBEYXRlIjpudWxsLCJQb3N0IE1vbnRoIjpudWxsLCJJbnZvaWNlIFN0YXR1cyI6bnVsbCwiUGF5bWVudFwvQ2hlY2sgIyI6IkFwcGxlIFRvdGFsOiIsIkR1ZSBEYXRlIjpudWxsLCJBbW91bnQiOjI4ODB9LHsiSW52b2ljZSBOdW1iZXIiOiJWZW5kb3I6IE1pY3Jvc29mdCIsIkludm9pY2UgRGF0ZSI6bnVsbCwiUG9zdCBNb250aCI6bnVsbCwiSW52b2ljZSBTdGF0dXMiOm51bGwsIlBheW1lbnRcL0NoZWNrICMiOm51bGwsIkR1ZSBEYXRlIjpudWxsLCJBbW91bnQiOm51bGx9LHsiSW52b2ljZSBOdW1iZXIiOiIxMDgzNjk0Mzk0NTQtMTEwNDIzIiwiSW52b2ljZSBEYXRlIjoiMjAyMy0xMS0wNFQwMDowMDowMCIsIlBvc3QgTW9udGgiOiIyMDIzLTExLTAxVDAwOjAwOjAwIiwiSW52b2ljZSBTdGF0dXMiOiJQb3N0ZWQiLCJQYXltZW50XC9DaGVjayAjIjpudWxsLCJEdWUgRGF0ZSI6IjIwMjMtMTEtMjciLCJBbW91bnQiOjU3MDcuMDR9LHsiSW52b2ljZSBOdW1iZXIiOm51bGwsIkludm9pY2UgRGF0ZSI6bnVsbCwiUG9zdCBNb250aCI6bnVsbCwiSW52b2ljZSBTdGF0dXMiOm51bGwsIlBheW1lbnRcL0NoZWNrICMiOiJNaWNyb3NvZnQgVG90YWw6IiwiRHVlIERhdGUiOm51bGwsIkFtb3VudCI6NTcwNy4wNH1d"),
    #"Imported JSON" = Json.Document(Source,1252),
    #"Converted to Table" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Invoice Number", "Invoice Date", "Post Month", "Invoice Status", "Payment/Check #", "Due Date", "Amount"}, {"Invoice Number", "Invoice Date", "Post Month", "Invoice Status", "Payment/Check #", "Due Date", "Amount"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Vendor", each if Text.StartsWith([Invoice Number], "Vendor: ") ?? false then Text.AfterDelimiter([Invoice Number], ": ") else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Vendor"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Invoice Status] <> null))
in
    #"Filtered Rows"