Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
each keyword is missing. Use below Table.SelectRows
Table.SelectRows([Custom.2], each [Column4] <> "Yearly")
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
each keyword is missing. Use below Table.SelectRows
Table.SelectRows([Custom.2], each [Column4] <> "Yearly")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |