Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
Solved! Go to 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.
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.
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
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
6 | |
6 |