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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
IbrarKaleem1
Frequent Visitor

Repeated rows to column

Dear Experts,

I am quite new to Power BI and trying to solve this from quite some time but no success.

please click on the link to  dataset and M-code : https://www.dropbox.com/scl/fo/dra5t6k9aeroqova2c9d9/h?rlkey=4hkwcdvqtqzzyaz8s14clovku&dl=0

I want to achieve something like below

IbrarKaleem1_0-1706793763650.png

 

So far, I reached to this point and now I am not sure how to change the repeated records of rows into Columns. Also attaching  the M-code.

IbrarKaleem1_3-1706794911900.png

 

I appreciate if community members can help me out.

 

Thanks,

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

Hi @IbrarKaleem1 

 

This is my solution. Hope it would be helpful!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE31DdU0lEKLU4tAlKJQACjYnWilYBsx+SSzPw8IMMzD6imBMGAynuWpOaCtBgaQUmouEtqcXJRZgFUc3J+aV5JZl46kFmWmJOZkgjmQJUGZ6bnJeYAGYZgDBKFOs0I4bQkIIBRpDkNhyyqA8tAAEjDZAOK8lNKk0sUfDLzUoHcEI9wQzMDBAOHy2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Field = _t, #"Old Value" = _t, #"New Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Field", type text}, {"Old Value", type text}, {"New Value", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),

    // get the first table for old values   
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Down",{"Date", "Field", "Old Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Field]), "Field", "Old Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Description", "Old Description"}, {"Signal", "Old Signal"}, {"Product Line", "Old Product Line"}}),
    
    // get the second table for new values   
    #"Removed Other Columns1" = Table.SelectColumns(#"Filled Down",{"Date", "Field", "New Value"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[Field]), "Field", "New Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"Description", "New Description"}, {"Signal", "New Signal"}, {"Product Line", "New Product Line"}}),
    
    // merge two tables
    FinalTable = Table.NestedJoin(#"Renamed Columns", {"Date", "User", "Action", "Item"}, #"Renamed Columns1", {"Date", "User", "Action", "Item"}, "New Values", JoinKind.FullOuter),
    #"Expanded New Values" = Table.ExpandTableColumn(FinalTable, "New Values", {"New Description", "New Signal", "New Product Line"}, {"New Description", "New Signal", "New Product Line"})
in
    #"Expanded New Values"

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @IbrarKaleem1, this one was really time consuming task 🙂

 

Result:

dufoq3_0-1706892930359.png

 

Don't forget to change address to your .txt file in 1st step Source

 

let
    //Change address to your File!
    Source = Csv.Document(File.Contents("C:\Users\a028311\OneDrive - Volvo Group\Downloads\PowerQueryForum\Repeated rows to column\ItemMasterChanges-BI.txt"),[Delimiter=",", Columns=1, Encoding=1250, QuoteStyle=QuoteStyle.None]),
    RemovedTopRowsDynamic = Table.Skip(Source, each not Text.StartsWith([Column1], "Date/Time")),
    FilteredOutBlank = Table.SelectRows(RemovedTopRowsDynamic, each ([Column1] <> "")),
    #"Added Index" = Table.AddIndexColumn(FilteredOutBlank, "Index", 0, 1, Int64.Type),
    #"Split Column by Positions" = Table.SplitColumn(#"Added Index", "Column1", Splitter.SplitTextByPositions({0, 25, 40, 51, 77, 91, 106}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}),
    Ad_DateTime = Table.AddColumn(#"Split Column by Positions", "Date Time", each DateTime.From(Text.Start([Column1.1], 17), "sk-SK"), type datetime),
    #"Removed Errors" = Table.RemoveRowsWithErrors(Ad_DateTime, {"Date Time"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Errors",{{"Column1.1", "Date"}, {"Column1.2", "Session"}, {"Column1.3", "User"}, {"Column1.4", "Transaction"}, {"Column1.5", "Sequence"}, {"Column1.6", "Action"}, {"Column1.7", "Status"}}),
    TrimCharacters = Table.TransformColumns(#"Renamed Columns", List.Transform(List.RemoveLastN(Table.ColumnNames(#"Renamed Columns"), 2), (colName)=>
     {colName, each Text.Trim(_, {" ", "|"}), type text})),
    #"Removed Columns" = Table.RemoveColumns(TrimCharacters,{"Date Time"}),
    StepBack = #"Added Index",
    #"Filtered Rows" = Table.SelectRows(StepBack, each Text.StartsWith([Column1], "   ") and not (Text.Contains([Column1], " +---") or Text.Contains([Column1], " |---"))  ),
    #"Split Column by Positions1" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByPositions({9, 25, 78, 106}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    TrimCharacters1 = Table.TransformColumns(#"Split Column by Positions1", List.Transform(List.RemoveLastN(Table.ColumnNames(#"Split Column by Positions1"), 1), (colName)=>
     {colName, each Text.Trim(_, {" ", "|"}), type text})),
    #"Promoted Headers" = Table.PromoteHeaders(TrimCharacters1, [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Field] <> "Field")),
    RenamedColumnsDynamic = Table.RenameColumns(#"Filtered Rows1", {{Table.ColumnNames(#"Filtered Rows1"){2}, "Old"}, {Table.ColumnNames(#"Filtered Rows1"){3}, "New"}, {Table.ColumnNames(#"Filtered Rows1"){4}, "Index"}}),
    #"Added Index1" = Table.AddIndexColumn(RenamedColumnsDynamic, "Index.1", 0, 1, Int64.Type),
    Ad_GeneratedMissingIndex = Table.AddColumn(#"Added Index1", "GeneratedMissingIndex", each try {#"Added Index1"{[Index.1]-1}[Index]..[Index]-1} otherwise {0..[Index]-1}, type list),
    #"Expanded GeneratedMissingIndex" = Table.ExpandListColumn(Ad_GeneratedMissingIndex, "GeneratedMissingIndex"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded GeneratedMissingIndex", {"GeneratedMissingIndex"}, #"Removed Columns", {"Index"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Date", "Session", "User", "Transaction", "Sequence", "Action", "Status"}, {"Date", "Session", "User", "Transaction", "Sequence", "Action", "Status"}),
    #"Filled Down" = Table.FillDown(#"Expanded Removed Columns",{"Date", "Session", "User", "Transaction", "Sequence", "Action", "Status"}),
    #"Filtered Rows2" = Table.SelectRows(#"Filled Down", each ([Date] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows2", {"Index.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates",{"Index", "Index.1", "GeneratedMissingIndex"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", type datetime}}, "sk-SK"),
    Ad_Item = Table.AddColumn(#"Changed Type", "Item", each if [Field Description] = "Item" then [Old] else null, type text),
    #"Filled Down1" = Table.FillDown(Ad_Item,{"Item"}),
    #"Filtered Rows3" = Table.SelectRows(#"Filled Down1", each ([Field Description] <> "Item")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows3","",null,Replacer.ReplaceValue,{"Old", "New"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Item"}, {{"All", each _, type table}}),
    Ad_TableOldNew = Table.AddColumn(#"Grouped Rows", "Table Old New", each Table.Combine({
     Table.TransformColumns(Table.RenameColumns(Table.RemoveColumns([All], {"New"}), {{"Old", "Value"}}), {{"Field Description", each "Old " & _, type text}}), //Old
     Table.TransformColumns(Table.RenameColumns(Table.RemoveColumns([All], {"Old"}), {{"New", "Value"}}), {{"Field Description", each "New " & _, type text}})  //New
}), type table),
    #"Removed Columns2" = Table.RemoveColumns(Ad_TableOldNew,{"All"}),
    #"Expanded Table Old New" = Table.ExpandTableColumn(#"Removed Columns2", "Table Old New", {"Field Description", "Value", "Date", "User", "Action"}, {"Field Description", "Value", "Date", "User", "Action"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Table Old New", List.Distinct(#"Expanded Table Old New"[#"Field Description"]), "Field Description", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Date", "User", "Action", "Item", "Old Description", "New Description", "Old Item Signal", "New Item Signal", "Old Product Line", "New Product Line", "Old Item Group", "New Item Group", "Old Item Type", "New Item Type", "Old Product Class", "New Product Class"})
in
    #"Reordered Columns"

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

v-jingzhan-msft
Community Support
Community Support

Hi @IbrarKaleem1 

 

This is my solution. Hope it would be helpful!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE31DdU0lEKLU4tAlKJQACjYnWilYBsx+SSzPw8IMMzD6imBMGAynuWpOaCtBgaQUmouEtqcXJRZgFUc3J+aV5JZl46kFmWmJOZkgjmQJUGZ6bnJeYAGYZgDBKFOs0I4bQkIIBRpDkNhyyqA8tAAEjDZAOK8lNKk0sUfDLzUoHcEI9wQzMDBAOHy2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Field = _t, #"Old Value" = _t, #"New Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Field", type text}, {"Old Value", type text}, {"New Value", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),

    // get the first table for old values   
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Down",{"Date", "Field", "Old Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Field]), "Field", "Old Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Description", "Old Description"}, {"Signal", "Old Signal"}, {"Product Line", "Old Product Line"}}),
    
    // get the second table for new values   
    #"Removed Other Columns1" = Table.SelectColumns(#"Filled Down",{"Date", "Field", "New Value"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[Field]), "Field", "New Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"Description", "New Description"}, {"Signal", "New Signal"}, {"Product Line", "New Product Line"}}),
    
    // merge two tables
    FinalTable = Table.NestedJoin(#"Renamed Columns", {"Date", "User", "Action", "Item"}, #"Renamed Columns1", {"Date", "User", "Action", "Item"}, "New Values", JoinKind.FullOuter),
    #"Expanded New Values" = Table.ExpandTableColumn(FinalTable, "New Values", {"New Description", "New Signal", "New Product Line"}, {"New Description", "New Signal", "New Product Line"})
in
    #"Expanded New Values"

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Hello @v-jingzhan-msft,

 

Thank you for the time and effort you put in to solve this. much appreciate. 

 

I have still 1 issues. There are some cases when the date/time value is exactly the same ( meaning 2 users changed the values at the same date/time) so the Date time is not unique. While pivoting i have some errors. which i am able to solve by below :

// creating a conditional column, if the cell value is "User" then 1 otherwise 0

#"Added Custom" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Merged] = "User" then 1 else 0),

// creating an index column starting with 1
#"Added Index1" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),

// creating a buffer list ( for optimzation)
#"Buffered List" = List.Buffer(#"Added Index1"[Custom.1]),

// Calculate the Running total
#"Added Custom1" = Table.AddColumn(#"Added Index1", "RunningTotal", each List.Sum(List.FirstN(#"Buffered List",[Index]))),

 

But still the performance is very slow. is there any other way you recommend to create a primary key.

 

Thanks.

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors