Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to Filter Columns from a binary

Hi guys, need some help on this

I have imported data source from a folder containing 2 excel workbooks.  So far it has been working well until the last step which is to filter to keep the columns that do not contain the word yearly. Just would like to check if im writing it correctly when importing folders instead of from a workbook. 

 

let
    Source = Folder.Files("C:\Users\achia\OneDrive - Lenovo\Desktop\Lenovo\Payroll\Data Cleansing"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom1", "Custom", each Table.SelectColumns([Data],{"Column2","Column3","Column4"})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.RemoveFirstN([Custom],9)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each Table.RemoveLastN([Custom.1],17)),
    #"Filter Rows" = Table.AddColumn(#"Added Custom3", "Custom.3", each Table.SelectRows([Custom.2], [Column4] <> "Yearly"))
in
    #"Filter Rows"

 

 

Table View
Example 1 of workbook

Thanks

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    each keyword is missing. Use below Table.SelectRows

    Table.SelectRows([Custom.2], each [Column4] <> "Yearly")

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    each keyword is missing. Use below Table.SelectRows

    Table.SelectRows([Custom.2], each [Column4] <> "Yearly")

     

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago
    let
    Source = Folder.Files("C:\Users\achia\OneDrive - Lenovo\Desktop\Lenovo\Payroll\Data Cleansing"),
    Custom1=#table(
                   3,
                   List.TransformMany(
                                      Source[Content],
                                      each List.TransformMany(
                                                              Excel.Workbook(_)[Data],
                                                              each Table.ToRows(
Table.SelectRows(Table.RemoveLastN(Table.Skip([[Column2],[Column3],[Column4]],9),17),each not Text.StartsWith(Text.From([Column4]??""),"Yearly"))),
                                                             (x,y)=>y),
                                      (x,y)=>y
                                      )
                   )
in
    Custom1