Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Unable to transform sub data in powerquery
I have a data like this with sub headings in a single sheet, i need to have them in PQ to sumarize how to convert the sub heading into a column
Hi, @lalithab
Did you checked the solution I provided?
Did it helped in any way? If yes, appreciate the kudos. 👍
if you find my reply helpful, accept this answer as solution.
thanks
Proud to be a Super User!
let
Source = Excel.Workbook(File.Contents("full_path_to_file"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Filtered Rows" = Table.SelectRows(Sheet1_Sheet, each ([Column2] <> null)),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Category] <> "Category"))
in
#"Filtered Rows1"
Well, below are the steps that you might want to follow to achieve your goal.
1. From the Data tab select "From table/Range" (assuming you've got the Excel sheet open)
2. Power Query window will be opened, now select the column that has the subheading, and from "Transform Tab" click "Unpivot"
3. Now use "Use first row as header" option and then rename the "Attribute" column to a proper name.
I am adding a GIF for your better understanding.
Follow same technique for the rest of your subheader in the sheet and then use "Append" queries to append the tables as your table structure is same.
Let me know if you have any further questions.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be a Super User!
Hi, @lalithab
Can you be more specific or share a screenshot if possible?
Proud to be a Super User!