Forum Discussion
hylosko
4 years agoHelper III
Power query table transformation
Hello!
It is any possibility to change table from screen1 to table from screen2 in PQ ?
screen1
screen2
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJzMsGUob6hvpGBkZGCoZGVgYGQKTg6AsShmIDPUsoGasTreSKrM8Imz4jkJSeEViPiQmYMjYC6w1F6DXWNzTHZykEgzSFoGjCpQeCDNGdiEMHAsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Vehicle = _t, #"Launch date" = _t, #"KPZ M1" = _t, #"KPZ M2" = _t, #"KPZ M3" = _t, KPZ = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Vehicle", type text}, {"Launch date", type datetime}, {"KPZ M1", Int64.Type}, {"KPZ M2", type number}, {"KPZ M3", type number}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"KPZ"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name", "Vehicle", "Launch date"}, "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Launch date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0,1), type table [Name=text, Vehicle=text, Launch date=nullable date, Value=number, Index=number]}}), #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Vehicle", "Launch date", "Value", "Index"}, {"Vehicle", "Launch date", "Value", "Index"}), #"Added Custom" = Table.AddColumn(#"Expanded Temp", "Month", each Date.AddMonths([Launch date],[Index])), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Name", "Vehicle", "Launch date", "Month", "Value"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "KPZ"}}) in #"Renamed Columns"
2 Replies
- Vijay_A_VermaMost Valuable Professional
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJzMsGUob6hvpGBkZGCoZGVgYGQKTg6AsShmIDPUsoGasTreSKrM8Imz4jkJSeEViPiQmYMjYC6w1F6DXWNzTHZykEgzSFoGjCpQeCDNGdiEMHAsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Vehicle = _t, #"Launch date" = _t, #"KPZ M1" = _t, #"KPZ M2" = _t, #"KPZ M3" = _t, KPZ = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Vehicle", type text}, {"Launch date", type datetime}, {"KPZ M1", Int64.Type}, {"KPZ M2", type number}, {"KPZ M3", type number}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"KPZ"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name", "Vehicle", "Launch date"}, "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Launch date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0,1), type table [Name=text, Vehicle=text, Launch date=nullable date, Value=number, Index=number]}}), #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Vehicle", "Launch date", "Value", "Index"}, {"Vehicle", "Launch date", "Value", "Index"}), #"Added Custom" = Table.AddColumn(#"Expanded Temp", "Month", each Date.AddMonths([Launch date],[Index])), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Name", "Vehicle", "Launch date", "Month", "Value"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "KPZ"}}) in #"Renamed Columns"- hyloskoHelper III
Big Thanks, it working well 🙂