Forum Discussion
Anonymous
7 years agoNot applicable
Need Help! Data transformation
Hi, Could anyone please help me to transform data in desired output in Power BI. Input Data ID COL1 COL1_Value COL2 COL2_Value COL3 COL3_Value 1 ServerName ABCDEF ServerName POI...
smpa01
7 years agoCommunity Champion
let
Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Need-Help-Data-transformation/m-p/649703#M311554")),
Data0 = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"COL1", type text}, {"COL1_Value", type text}, {"COL2", type text}, {"COL2_Value", type text}, {"COL3", type text}, {"COL3_Value", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "COL1", "COL1_Value"}),
Custom1 = Table.SelectColumns(#"Changed Type",{"ID", "COL2", "COL2_Value"}),
#"Renamed Columns" = Table.RenameColumns(Custom1,{{"COL2", "COL1"}, {"COL2_Value", "COL1_Value"}}),
Custom2 = Table.SelectColumns(#"Changed Type",{"ID", "COL3", "COL3_Value"}),
#"Renamed Columns1" = Table.RenameColumns(Custom2,{{"COL3", "COL1"}, {"COL3_Value", "COL1_Value"}}),
Custom3 = #"Removed Other Columns"&#"Renamed Columns"&#"Renamed Columns1",
#"Counted Rows" = Table.RowCount(Custom3)/3,
Custom4 = List.Repeat({"ServerName","DiskName","Application"}, #"Counted Rows"),
Custom5 = Table.FromColumns(Table.ToColumns(Custom3)&{Custom4}),
#"Removed Columns" = Table.RemoveColumns(Custom5,{"Column2"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Column4"}, {{"Count", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"IX",1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"IX"}, {"IX"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"IX"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1"),
#"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom", each {"ServerName", "DiskName","Application"}),
#"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
#"Added Index1" = Table.AddIndexColumn(#"Expanded Custom1", "Index", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Custom", "Index"},#"Added Index",{"Column4", "Index"},"Added Index1",JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Column3"}, {"Column3"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded Added Index1",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Custom]), "Custom", "Column3"),
#"Renamed Columns2" = Table.RenameColumns(#"Pivoted Column",{{"IX", "ID"}})
in
#"Renamed Columns2"