none
Transformation on invoicing data RRS feed

Answers

  • This code should work:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type any}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type number}, {"Column11", type text}, {"Column12", type text}, {"Column13", type any}, {"Column14", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "HeaderRow", each if [Column7] = null then null else "Header1"),
        #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each try if Number.From([Column5]) > 0 then null else [Column5] otherwise [Column5]),
        #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom.1"}),
        #"Filled Down" = Table.FillDown(#"Filled Up",{"Column14", "Column6"}),
        #"Added Custom1" = Table.AddColumn(#"Filled Down", "Comp Code", each if [HeaderRow] = null then null else [Column4]),
        #"Added Conditional Column1" = Table.AddColumn(#"Added Custom1", "Date", each if [HeaderRow] = null then null else [Column5]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Date", type date}}),
        #"Added Conditional Column2" = Table.AddColumn(#"Changed Type1", "Invoice", each if [HeaderRow] = null then null else [Column9]),
        #"Filled Down1" = Table.FillDown(#"Added Conditional Column2",{"Comp Code", "Date", "Invoice"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Column10] <> null) and ([Column9] <> null)),
        #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column14", "Cust number"}, {"Column12", "Text"}, {"Column11", "Indicator"}, {"Column9", "Doc Type"}, {"Column10", "Amount"}, {"Column6", "ProfitCentre"}, {"Column5", "CostCentre"}, {"Custom.1", "Description"}, {"Column4", "Account"}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Account", "CostCentre", "ProfitCentre", "Doc Type", "Amount", "Indicator", "Text", "Cust number", "Description", "Comp Code", "Date", "Invoice"})
    in
        #"Removed Other Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:32 AM
    Thursday, April 11, 2019 1:16 PM
    Moderator
  • I was expecting you would take a different approach in solving the problem. Having said that, couple of if statments and fill down solved the problem. Thanks again Imke.
    • Marked as answer by M.Awal Friday, April 12, 2019 9:26 AM
    Friday, April 12, 2019 9:26 AM

All replies

  • To

    Wednesday, April 10, 2019 11:07 AM
  • Sorry guys. Screen shots are showing up.
    Wednesday, April 10, 2019 11:09 AM
  • Hi : Take three, see if it works this time I am using different browser. Any help would appreciated. Thanks

    From:

    To:

    Thursday, April 11, 2019 11:07 AM
  • This code should work:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type any}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type number}, {"Column11", type text}, {"Column12", type text}, {"Column13", type any}, {"Column14", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "HeaderRow", each if [Column7] = null then null else "Header1"),
        #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each try if Number.From([Column5]) > 0 then null else [Column5] otherwise [Column5]),
        #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom.1"}),
        #"Filled Down" = Table.FillDown(#"Filled Up",{"Column14", "Column6"}),
        #"Added Custom1" = Table.AddColumn(#"Filled Down", "Comp Code", each if [HeaderRow] = null then null else [Column4]),
        #"Added Conditional Column1" = Table.AddColumn(#"Added Custom1", "Date", each if [HeaderRow] = null then null else [Column5]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Date", type date}}),
        #"Added Conditional Column2" = Table.AddColumn(#"Changed Type1", "Invoice", each if [HeaderRow] = null then null else [Column9]),
        #"Filled Down1" = Table.FillDown(#"Added Conditional Column2",{"Comp Code", "Date", "Invoice"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Column10] <> null) and ([Column9] <> null)),
        #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column14", "Cust number"}, {"Column12", "Text"}, {"Column11", "Indicator"}, {"Column9", "Doc Type"}, {"Column10", "Amount"}, {"Column6", "ProfitCentre"}, {"Column5", "CostCentre"}, {"Custom.1", "Description"}, {"Column4", "Account"}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Account", "CostCentre", "ProfitCentre", "Doc Type", "Amount", "Indicator", "Text", "Cust number", "Description", "Comp Code", "Date", "Invoice"})
    in
        #"Removed Other Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:32 AM
    Thursday, April 11, 2019 1:16 PM
    Moderator
  • I was expecting you would take a different approach in solving the problem. Having said that, couple of if statments and fill down solved the problem. Thanks again Imke.
    • Marked as answer by M.Awal Friday, April 12, 2019 9:26 AM
    Friday, April 12, 2019 9:26 AM