Forum Discussion
Anonymous
3 years agoNot applicable
Selectcolumns and append in same Power Query.
I have a table like this Group Limit Americas Americas-Value Europe Europe-Value A 750 80 20 25 10 B 1000 50 60 90 50 I want to transform it into Group Limit Regio...
- 3 years ago
Anonymous , Please use this code in blank query in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI3NQCSFiDCCEyYAglDA6VYnWglJzDTACQMVmUGIiyh3NhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Limit = _t, Americas = _t, #"Americas-Value" = _t, Europe = _t, #"Europe-Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Limit", Int64.Type}, {"Americas", Int64.Type}, {"Americas-Value", Int64.Type}, {"Europe", Int64.Type}, {"Europe-Value", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group", "Limit"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Text.Contains([Attribute], "Value") then "Actual Value" else "Regional value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Added Custom", {{"Attribute", each Text.BeforeDelimiter(_, "-"), type text}}), #"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Custom]), "Custom", "Value", List.Sum) in #"Pivoted Column"
amitchandak
3 years agoSuper User
Anonymous , Please use this code in blank query in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI3NQCSFiDCCEyYAglDA6VYnWglJzDTACQMVmUGIiyh3NhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Limit = _t, Americas = _t, #"Americas-Value" = _t, Europe = _t, #"Europe-Value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Limit", Int64.Type}, {"Americas", Int64.Type}, {"Americas-Value", Int64.Type}, {"Europe", Int64.Type}, {"Europe-Value", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group", "Limit"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Text.Contains([Attribute], "Value") then "Actual Value" else "Regional value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Added Custom", {{"Attribute", each Text.BeforeDelimiter(_, "-"), type text}}),
#"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Custom]), "Custom", "Value", List.Sum)
in
#"Pivoted Column"