Issues with getting data from excel to PBI report

[Syphimus99]

I can't figure out how to get data from an Excel file to a PBI report as a table with different hierarchical categories while there are other columns to be begin with. basically, there are 2 main categories and they have subcategories like 1.1, 1.2, 1.3 and 2.1, 2.2 and even some of those subcategories have few categories inside them such as 2.1.1, 2.1.2 and 2.2.1

Would anyone know how to integrate them into PowerBI? side note: I am not allowed to alter the excel file structure in any way?

UPDATE: I solved it on my own, thanks

[ToddChitt]

Hello @Syphimus99 

Can we infer from your first screenshot that rows 6 through 13 belong to Category 1.1? If so, then maybe you can do something like this:

* Create a new custom column that returns Column B, but ONLY fi Column B is a Category Id, such as "1.1". I leave it up to you to write the Power Query M statement that will do that. It should return NULL if Column B is NOT a Category Id. 

* Now do a Fill Down on the new column.

* Lastly, get rid of any rows where the new column = Column B.

[Syphimus99]

Yes, rows 6 through 13 belong to Category 1.1

I will try your method, thanks for the advice, Will update you if I manage to get it right

[lbendlin]

That is a rather unfortunate format.  You can modify it in Power Query though, to make it usable.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Syphimus99]

Hey, I Updated my post, there is a link to the sample data, would this be suitable for you?

[lbendlin]

Do you need the hungarian task names in column A?

[Syphimus99]

unfortunately yes, the headers from row 3 need to remain as columns for all of the categories

[lbendlin]

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"

[Syphimus99 · Accepted solution]

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.