Forum Discussion
Issues with getting data from excel to PBI report
your code seems to work up until the last line "
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Category", "Subcategory"})"
I don't seem to understand how do i insert the logic that there are multiple categories and their subcategories.
I updated the excel file i shared you in the hyperlink, so now the categories name are accurate if you delete the numbers inside the brackets next to their name, Example: EAST BLOCK(Category 1)
true value: EASTBLOCK
and the same goes for the other categories and subcategories.
unfortunately yes, the headers from row 3 need to remain as columns for all of the categories
let
Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\File (1).xlsx"), null, true),
Sample_Sheet = Source{[Item="Sample",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sample_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Project name", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Project name", "Column3", "Column4", "Column5", "Column6"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Project name] <> null)),
#"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Task HU", type text}, {"Tasks", type any}, {"Duration (calendar days)", Int64.Type}, {"Start date", type date}, {"Planned Finish date", type date}, {"Comments", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Category", each try if Text.StartsWith([Tasks],"Category") then [Tasks] else null otherwise null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Subcategory", each try if List.Contains({"0".."9"},Text.Start(Text.From([Tasks]),1)) then [Tasks] else null otherwise null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Category", "Subcategory"})
in
#"Filled Down"your code seems to work up until the last line "
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Category", "Subcategory"})"
I don't seem to understand how do i insert the logic that there are multiple categories and their subcategories.
I updated the excel file i shared you in the hyperlink, so now the categories name are accurate if you delete the numbers inside the brackets next to their name, Example: EAST BLOCK(Category 1)
true value: EASTBLOCK
and the same goes for the other categories and subcategories.