Repeated rows to column

[Anonymous]

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

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.

I appreciate if community members can help me out.

Thanks,

[dufoq3]

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

Result:

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"

[Anonymous · Accepted solution]

Hi @Anonymous 

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!

[Anonymous]

Hello @Anonymous,

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.