How to transform / unpivot columns to keep an attribute, value and date

[lukedaly01]

Folks,

I've a report that I'm trying to transform so that it will display an attribute, a value and more importantly a timestamp. If it was just the attribute and value it would not be a problem as I could use the unpivot other colums function. I'm struggling to find a way to keep the datestamp. I've displayed the "Before" and "After" tables below. Any help would be appreciated.

Name	Reports To	Task 1	Task1 DateStamp	Task 2	Task 2 DateStamp	Task 3	Task 3 DateStamp
John P	Mark S	Beginner	13/04/2023	Stage 2	15/06/2024	Stage 4	17/08/2023
Michael H	Joe B	Stage 1	14/05/2023	Stage 3	16/07/2024	Stage 5	18/09/2023

Require to display like this:

Name	Reports To	Attribute	Value	Date
John P	Mark S	Task 1	Beginner	13/04/2023
John P	Mark S	Task 2	Stage 2	15/06/2024
John P	Mark S	Task 3	Stage 4	17/08/2023
Michael H	Joe B	Task 1	Stage 1	14/05/2023
Michael H	Joe B	Task 2	Stage 3	16/07/2024
Michael H	Joe B	Task 3	Stage 5	18/09/2023

[foodd · Accepted solution]

Hello @lukedaly01 , remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[dufoq3]

Hi @lukedaly01, another 2 solutions here:

Result:

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzNCoQwDAZfJfQsJK2tP1dPIgiCx+KhLEFlpULx/dmGFcHbZDJ83qvh3CJMqlBjSF+YM3S87jFyyqhLJIuGTJmP+QorgxHtkCrR9tFCukZq/vVSeDXuny3wAX1+DSdD98RaYovk3tNCukKq39NOdIPU3tPLDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Reports To" = _t, #"Task 1" = _t, #"Task1 DateStamp" = _t, #"Task 2" = _t, #"Task 2 DateStamp" = _t, #"Task 3" = _t, #"Task 3 DateStamp" = _t]),
    Transformed = List.TransformMany(
        Table.ToRows(Source),
        each List.Split(List.Skip(_, 2), 2),
        (x,y)=> {x{0}} & {x{1}} & y
),
    ToTable = Table.FromRows(Transformed, type table[Name=text, Reports To=text, Value=text, Date=date]),
    TaskColumn = [ a = List.Alternate(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Task")),1,1,1),
    b = List.Repeat(a, Table.RowCount(ToTable) / List.Count(a))
  ][b],
    Merged = Table.FromColumns(Table.ToColumns(ToTable) & {TaskColumn}, Value.Type(ToTable & #table(type table[Attribute=text], {}))),
    ChangedType = Table.TransformColumnTypes(Merged,{{"Date", type date}})
in
    ChangedType

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzNCoQwDAZfJfQsJK2tP1dPIgiCx+KhLEFlpULx/dmGFcHbZDJ83qvh3CJMqlBjSF+YM3S87jFyyqhLJIuGTJmP+QorgxHtkCrR9tFCukZq/vVSeDXuny3wAX1+DSdD98RaYovk3tNCukKq39NOdIPU3tPLDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Reports To" = _t, #"Task 1" = _t, #"Task1 DateStamp" = _t, #"Task 2" = _t, #"Task 2 DateStamp" = _t, #"Task 3" = _t, #"Task 3 DateStamp" = _t]),
    TaskColNames = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Task")),
    Splitted = List.Buffer(List.Split(TaskColNames, 2)),
    MergedColumnsDynamic = List.Accumulate(
        Splitted,
        Source,
        (s,c)=> Table.CombineColumns(s, {c{0}, c{1}},Combiner.CombineTextByDelimiter("||", QuoteStyle.None), c{0})
),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(MergedColumnsDynamic, {"Name", "Reports To"}, "Attribute", "Value"),
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedOtherColumns, "Value", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Value", "Date"}),
    ChangedType = Table.TransformColumnTypes(SplitColumnByDelimiter,{{"Date", type date}})
in
    ChangedType

[lukedaly01]

Thank you shafiz_p, your code worked and gave me the desired results 🙂

[shafiz_p · Accepted solution]

@lukedaly01 Use the code below to resolve the problem :

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedTypewithLocale = Table.TransformColumnTypes(Source, {{"Task1 DateStamp", type date}, {"Task 2 DateStamp", type date}, {"Task 3 DateStamp", type date}}, "en-GB"), 
UnpivotColumns = Table.UnpivotOtherColumns(ChangedTypewithLocale, {"Name", "Reports To"}, "Attributes", "Value"),
Step1 = Table.AddColumn(
UnpivotColumns,
"Values",
each if Value.Is([Value], type date) then null
else [Value], type text
),
Step2 = Table.AddColumn(
Step1,
"Date",
each if Value.Is([Value], type text) then null
else [Value],type date
),
SplitAttr = Table.SplitColumn(Step2, "Attributes", Splitter.SplitTextByDelimiter("D", QuoteStyle.Csv),{"Attribute","Att.1"}),
RemoveColumn = Table.RemoveColumns(SplitAttr, {"Value","Att.1"}),
FilledDown = Table.FillDown(RemoveColumn,{"Values"}),
FilteredRows = Table.SelectRows(FilledDown, each ([Date] <> null)),
TypeChange = Table.TransformColumnTypes(FilteredRows, {{"Name", type text}, {"Reports To", type text}})

in
TypeChange


Please set your own locale for date.

Hope this help!!

If this solved your problem, please mark it as a solution!!

[foodd · Accepted solution]

Hello @lukedaly01 , remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[lukedaly01]

Will do, thanks for the info. I'll close this and start a new topic

[foodd]

Hello @lukedaly01 , you don't need to start a new topic, rather share a link to your Source File and a Work in Progress PBIX.

[lukedaly01]

Link to source file: https://www.dropbox.com/scl/fi/ywkhhd5l2hjyy6gzxv6nc/Source.xlsx?rlkey=pr64przmch386zpmcfaq8lvpd&st=...

Link to pbix: https://www.dropbox.com/scl/fi/erg6x2h4ucz0t8phic1fo/SamplePBi.pbix?rlkey=tk7lnlpuwqd76rjkcuovn58iz&...