Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
CoffeeCup1
New Member

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 after the end of each vendor. I want to get rid of those extra rows and put the vendor info in its own column next to each invoice. I don't need the total data at all.

 

How can I do this in PowerQuery?

 

What it looks like now:

CurrentCurrent

 

What I want the result to look like.

What I want to look likeWhat I want to look like

1 ACCEPTED SOLUTION
ronrsnfld
Community Champion
Community Champion

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:

 

ronrsnfld_0-1701210447760.png

 

 

View solution in original post

3 REPLIES 3
CoffeeCup1
New Member

Thank you, all! It worked! 🙂

spinfuzer
Solution Sage
Solution Sage

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"

 

ronrsnfld
Community Champion
Community Champion

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:

 

ronrsnfld_0-1701210447760.png

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors