Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
hylosko
Helper III
Helper III

Power query table transformation

Hello!

 

It is any possibility to change table from screen1 to table from screen2 in PQ ?

 

screen1

hylosko_0-1653902619602.png

screen2

hylosko_1-1653902648929.png

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

 

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

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"

 

Big Thanks, it working well 🙂 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors