Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello all!
I have a small puzzle that concerns inserting rows.
1 | A |
2 | A |
3 | B |
4 | B |
5 | C |
6 | C |
7 | C |
Notice in Column2, we have categorical data that varies in the number of instances that each category appears.
I'm trying to figure out a way that we can insert rows to makes the number of instances for each category equal. Since 'C' shows up 3 times, I would like to insert rows such that 'A' and 'B' show up 3 times as well.
The result table would be:
1 | A |
2 | A |
A | |
3 | B |
4 | B |
B | |
5 | C |
6 | C |
7 | C |
Column1 would have rows inserted into it that are blank, and Column2 would have an equal set of instances for each category (in this case, 3 instances each).
I'm a little new at this, and the documentation isn't all that helpful for beginners.
Thank you!
Solved! Go to Solution.
Solution uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuR4l1xMoEZxIPGuu?e=KNpNhf
Use below query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Column2", "Column2 - Copy"),
#"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each {1..List.Max(#"Duplicated Column"[Count])-[Count]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Custom] <> null),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column2 - Copy"}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Removed Other Columns"}),
#"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]=null then [#"Column2 - Copy"] else [Column2]),
#"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Custom", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column2", "Column2 - Copy", "Index"})
in
#"Removed Columns"
Solution uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuR4l1xMoEZxIPGuu?e=KNpNhf
Use below query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Column2", "Column2 - Copy"),
#"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each {1..List.Max(#"Duplicated Column"[Count])-[Count]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Custom] <> null),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column2 - Copy"}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Removed Other Columns"}),
#"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]=null then [#"Column2 - Copy"] else [Column2]),
#"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Custom", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column2", "Column2 - Copy", "Index"})
in
#"Removed Columns"
I learned a lot from this,
Thank you very much!
I see on the third line for "#GRouped Rows", there is a section that says
Table.RowCount(_)
Do you understand why there is an underscore in this function?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |