TRANSPOSE OR PIVOT

Question

Hello Community,I am currently working on a data transposition task that I find challenging. I have two data sources: one is a table that I query directly from a SQL database, and the other is a table that I query from an Excel file stored on my SharePoint.To simplify my work and have all the data in one place, I've appended the data from these two tables into a single combined table. Now, I am working with this combined table, which contains all the necessary data from both the SQL database and the Excel file.However, I am facing difficulties in transposing the data and also couting the line such dynamically such a way that if they add a new row it doesn't required to add it manually in this combined table. I would appreciate any assistance to help me to get this particular output table.i have used a Matrix but not still not getting the output table.&nbsp;&nbsp;this is my main combined table.Grant CodePayment MilestoneDate of Paymentg124100,000&nbsp;2/4/2024&nbsp;g124500&nbsp;2/4/2024&nbsp;g126&nbsp;700&nbsp; &nbsp;2/5/2017&nbsp;g1268002/5/2020&nbsp; &nbsp; &nbsp;g127782,000&nbsp;2/5/2021&nbsp;g12769,222&nbsp; &nbsp; &nbsp; &nbsp;2/5/2020&nbsp;g128150,000&nbsp;2/5/2023&nbsp; &nbsp;g129150,000&nbsp; 2/5/2024&nbsp; &nbsp;&nbsp;This is the final output needed&nbsp;| Grant Code | Payment Milestone 1 | Date of Payment M1 | Payment Milestone 2 | Date of Payment M2|-----------&nbsp; -|-------------------------|------------------ -----|------------------- -----|---------------------- || g124&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 100,000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2/4/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2/4/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| g126&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 700&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2/5/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 800&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2/5/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp;| g127&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 782,000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2/5/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 69,222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 2/5/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| g128&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 150,000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2/5/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || g129&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 150,000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 2/5/2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp;please i'ld really appreciate u're assistance&nbsp;&nbsp;

lbendlin · Accepted Answer

You should not really do that in Power Query.&nbsp; Preferably you would keep the data in a usable format like this
&nbsp;

&nbsp;
and then do the pivoting in the Matrix visual.
&nbsp;
But if you insist...
&nbsp;

&nbsp;
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7JDYAwDARbsfyOxGbJRS0ob/rvAIcjAST8WnnGx7rq5hnUqQccALHIKUwEg2h1nccflqyZjclJo1GfX7QAHbGJ0nFuw4Xj8OH4F0+LIylnPRfdUmnfR3yWzI8zyzAsiVxK0Fp3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Grant Code" = _t, #"Payment Milestone" = _t, #"Date of Payment" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Grant Code"}, {{"Index", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Payment Milestone", "Date of Payment", "Index"}, {"Payment Milestone", "Date of Payment", "Index.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Index",{{"Index.1", Int64.Type}, {"Date of Payment", type date}, {"Payment Milestone", Currency.Type}},"en-GB"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index.1", "Grant Code"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each [Attribute] &amp; " " &amp; Text.From([Index.1]),Replacer.ReplaceValue,{"Attribute"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Grant Code", "Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Forum Discussion

TRANSPOSE OR PIVOT

2 Replies

Related content

Pivot/ transpose

Help Transposing

Transpose & Pivot using Power Query

Pivoting/transposing problem

pivot or transpose?

Grant Code	Payment Milestone	Date of Payment
g124	100,000	2/4/2024
g124	500	2/4/2024
g126	700	2/5/2017
g126	800	2/5/2020
g127	782,000	2/5/2021
g127	69,222	2/5/2020
g128	150,000	2/5/2023
g129	150,000	2/5/2024