Forum Discussion
Solved
Aligning / Carrying forward data in a table
Hi Everyone I have data which has a modification date, modification field name and the corresponding data. So people create address data (for instance) for an employee. But the fields are not co...
Hi,
Edit 2nd step YourSource = Source and refer your data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvUNMDBU0lEy1PVKzNM1MgYyg3MzSzIUgkuKUlNLgFwFJKwUq4NVC0jONzEzryS/PI9I5SDsmlhcElySWJJKpHoQNjUwMMBU6paahFDqkZmegd8lvolFEOVOicnZRHnVsQCqwy+1nDQNIDmnzHT8DkJRDcLhqXiDBkM9CJvBgiYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Modification Date" = _t, Address = _t, Suburb = _t, State = _t, Zip = _t, Column1 = _t]), YourSource = Source, AddedSuburbSuffix = Table.TransformColumns(YourSource, {{"Suburb", each if Text.Trim(Text.From(_)) <> "" then Text.Trim(Text.From(_)) & ", " else "", type text}}), ReplaceBlankToNull = Table.TransformColumns(AddedSuburbSuffix, List.Transform(Table.ColumnNames(AddedSuburbSuffix), (colName)=> { colName, each if Text.Trim(_) = "" then null else Text.Trim(_), type text } ) ), ColNamesToFill = List.Buffer(List.Select(Table.ColumnNames(ReplaceBlankToNull), each not List.Contains({"Employee", "Modification Date"}, _))), StepBack = ReplaceBlankToNull, FilledDown = Table.FillDown(StepBack, ColNamesToFill), FilledUp = Table.FillUp(FilledDown, ColNamesToFill), #"Grouped Rows" = Table.Group(FilledUp, {"Employee", "Modification Date"}, {{"Address", each Text.Combine(Record.ToList(Table.Last(Table.SelectColumns(_, ColNamesToFill))), " "), type text}}) in #"Grouped Rows"Import your csv. Rename it to MyData. Then create blank query. Open Advanced Editor and replace whole code with the one I created. Close Advanced editor and edit 2nd step called YourSource.
Now it is = Source. Change it to = MyData
Hi,
Unpivot, Pivot, FillDown into Group, Expand and CombineColumns
let
Source = Prev_Step,
Unpivot = Table.UnpivotOtherColumns(Source, {"Employee", "Modification Date"}, "Attribute", "Value"),
Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[Attribute]), "Attribute", "Value"),
Group = Table.Group(Pivot, {"Employee"}, {{"Data", each Table.FillDown(_,{"Address", "Suburb", "State", "Zip"})}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Modification Date", "Address", "Suburb", "State", "Zip"}, {"Modification Date", "Address", "Suburb", "State", "Zip"}),
Result = Table.CombineColumns(Table.TransformColumnTypes(Expand, {{"Zip", type text}}),{"Address", "Suburb", "State", "Zip"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Result")
in
Result
Stéphane
