Forum Discussion
Pivot...UnPivot??? How to fix this bad data setup
Use this. That line was not needed.
let Source =Table.FromRows(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]), Custom1 = [a = List.Skip(Table.ToColumns(Source)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c] in Custom1I think what yoy are asking for is result for a group of Parent ID. Then use this
let Source =Table.FromRows(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]), Custom1 = Table.Combine(Table.Group(Source, {"ParentId"}, {"All", each [a = List.Skip(Table.ToColumns(_)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c]})[All]) in Custom1
Use this. That line was not needed.
let
Source =Table.FromRows(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]),
Custom1 = [a = List.Skip(Table.ToColumns(Source)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c]
in
Custom1
After examining the data, I ran into issues. Unfortunately, the data is so messed up, I have null values in the new and old value columns. Sample:
| Date | OldValue | NewValue |
| 6/28/23 | Submitted | Incomplete |
| 5/4/2024 | Incomplete | |
| 6/10/2024 | Withdrawn |
Is there a way to deal with that?
This is a very standard process with clear rules for identifying snapshot values
1. if there is no entry in the field history that means the current value is used
2. if there are entries in the field history before the snapshot date then use the New Value of the latest entry
3. if there are entries in the field history after the snapshot date then use the Old Value of the earliest entry
4. if the target field is blank then move on to the next extry
What is the output expected from the sample you just posted? Accordingly, I can change the code.
Hello,
I think I figured it out. Before I insert code you provided, I need to have the table sorted in in Hx Id and CreatedDate order.
Example:
This was the table straight from source:
After adding custom step - note the null value next to Response Required it should be 11/22/2023 12:00:00 AM, not null
After inserting a step to sort the data by ascending CreatedDate and Id, I get this after the Custom Step, which is the correct version of the history:
So thank you so much. All is well.
