Forum Discussion
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/dra5t6k9aeroq...
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!
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"