Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
i'm trying to transpose data like the picture in attached. But i can not do like i wanted (below in the pic).
Is it possible to implement this ?
thansk,
JR
Solved! Go to Solution.
maybe you can create a new table
Table 2 =
VAR tbl1=SELECTCOLUMNS('Table',"Costs",'Table'[Costs],"LEG",'Table'[LEG 1],"Date",'Table'[Date 1])
VAR tbl2=SELECTCOLUMNS('Table',"Costs",'Table'[Costs],"LEG",'Table'[LEG 2],"Date",'Table'[Date 2])
VAR tbl3=SELECTCOLUMNS('Table',"Costs",'Table'[Costs],"LEG",'Table'[LEG 3],"Date",'Table'[Date 3])
return UNION(tbl1,tbl2,tbl3)
Proud to be a Super User!
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Costs", Int64.Type}, {"LEG 1", type text}, {"Date 1", type date}, {"LEG 2", type text}, {"Date 2", type date}, {"LEG 3", type text}, {"Date 3", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Costs"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Costs", type text}}, "en-IN"),{"Costs", "Attribute.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 1, 1, Int64.Type),
Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index", "Index1"}, {"Value", "Index", "Index1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Merged.2]), "Merged.2", "Value"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Merged.1", "Costs"}})
in
#"Renamed Columns"
Hope this helps.
maybe you can create a new table
Table 2 =
VAR tbl1=SELECTCOLUMNS('Table',"Costs",'Table'[Costs],"LEG",'Table'[LEG 1],"Date",'Table'[Date 1])
VAR tbl2=SELECTCOLUMNS('Table',"Costs",'Table'[Costs],"LEG",'Table'[LEG 2],"Date",'Table'[Date 2])
VAR tbl3=SELECTCOLUMNS('Table',"Costs",'Table'[Costs],"LEG",'Table'[LEG 3],"Date",'Table'[Date 3])
return UNION(tbl1,tbl2,tbl3)
Proud to be a Super User!
Thanks Ryan_mayu, 😀
you are welcome
Proud to be a Super User!
@jppuam , Unpivot all column other than first.
Split column by delimiter or use text before delimiter and get Leg and Date from new attribute column
Pivot the attribute and value gain
Split Column Power Query: https://youtu.be/FyO9Vmhcfag
Text Before Delimiter and Text After Delimiter: https://youtu.be/oGY4RoPKDOE
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |