Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Apologies if this has been asked before or is too basic, I'm unable to solve it.
How am I able to convert the following table
Names | 01/02 | 01/02 | 02/02 | 02/02 |
| Qty | Code | Qty | Code |
X | 10 | E10 | 10 | E10 |
Y | 10 | E10 | 10 | E10 |
to
Dates | Names | Qty | Code |
01/02 | X | 10 | E10 |
02/02 | Y | 10 | E10 |
Hi,
IF what you really want is:
And your input table looks like:
then one option is:
let
Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content], // CHANGE table name here
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Column1"}, {{"All", each _, type table [Column1=nullable text, Attribute=text, Value=any]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows(#"Grouped Rows", each [Column1] = null)[All]{0}[Value]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.SelectRows(#"Grouped Rows", each [Column1] = "Names")[All]{0}[Value]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.FromColumns(Table.ToColumns([All]) & {[Custom]} & {[Custom.1]})),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Column1] <> null) and ([Column1] <> "Names")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Custom.2"}),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.2", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom.2", List.Distinct(#"Expanded Custom.2"[Column3]), "Column3", "Column2"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Column4", "Dates"}, {"Column1", "Names"}})
in
#"Renamed Columns"
Please mark this as answer if it helped.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
67 | |
25 | |
18 | |
12 |