Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Syphimus99
Regular Visitor

Issues with getting data from excel to PBI report

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
1 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.

View solution in original post

8 REPLIES 8
ToddChitt
Super User
Super User

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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
Super User
Super User

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...

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

Do you need the hungarian task names in column A?

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.