Data Formatting - Transpose Repeated Rows into Columns

[anshpalash]

Hi,

I have data in this format.  This is just one file.

I want to combine multiple files with similar format. The problem is that each file has different amount of ID numbers. I am having trouble figuring out a code that essentially tells power BI to transpose the repeated rows after the 'Parameters' row above.

For this file, I want format to be something like:

Below this file, new files should be merged into this which would have different IDs and Dates. The number of IDs in each file is different. Would really appreciate if someone could help me figure out how to format the data. 

[v-yetao1-msft · Accepted solution]

Hi @anshpalash 

Based on the file you provided, I created a sample.

(1)I create three tables , below is a screenshot of the original data that you provided .

(2)Go to Power Query ,then click Merge Queries as New , merge table ‘Sale’ and ‘Parameters’ based on field “ID” .

(3)Expended Parameters  

(4)Remove the duplicate column [Parameters.ID] and pivot [Parameters.Column2] [Parameters.Column3]

(5)Append table ‘Merge1’ and table ‘Date’ to return a new table ‘Append 1’ .

(6)Replace the value of column [Date]

(7)Remove bottom row and Close & Apply to Desktop view .

(8)Add a table visual with table ‘Append 1’ , the final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@anshpalash here it is, you can make this as a function and send file content as a parameter to the function and it will return a table output.

For testing, create a blank query, click advanced editor and paste this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUQKhWJ1oJSMDI0NdAwtdA0MkQSSmpwuQFZyYkwoTAKkzNIBrB/FMYTxjIMMIzjMB8YxhvIDEosTc1JLUomIk00GG+eQnJ5Zk5ucBmY6lxSWZeXCZkMoCkLVhqRmZyUAHwOxD0uCZl1IJF4cqd8pPLIG7BkltcGKegld+cSpcDovxJqha/CLhojDDM7OBKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Group", each if [Column1] = "Date" or [Column1] = "ID" or [Column1] = "Parameters" then [Column1] else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Group"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Date" and [Column1] <> "ID" and [Column1] <> "Parameters")),
    #"Date Table" = Table.SelectRows(#"Filtered Rows1", each [Group] = "Date"),
    #"Date" = #"Date Table"{0}[Column1],
    #"Parameters Table" = Table.SelectRows(#"Filtered Rows1", each [Group] = "Parameters"),
    #"Removed Columns" = Table.RemoveColumns(#"Parameters Table",{"Group"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column2]), "Column2", "Column3"),
    #"ID Table" = Table.SelectRows(#"Filtered Rows1", each [Group] = "ID"),
    #"Merged Queries" = Table.NestedJoin(#"ID Table", {"Column1"}, #"Pivoted Column", {"Column1"}, "Parameters Table", JoinKind.LeftOuter),
    #"Expanded Parameters Table" = Table.ExpandTableColumn(#"Merged Queries", "Parameters Table", {"Location", "Type"}, {"Location", "Type"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Parameters Table", "Date", each #"Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Column3", "Group"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "ID"}, {"Column2", "Sale"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sale", Currency.Type}, {"Date", type date}})
in
    #"Changed Type1"

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[anshpalash]

Hi @parry2k , should I paste the code into the 'Transform Sample File' query or my main table query? I will be adding more files to the folder from which power bi is getting the file. Thanks!