Forum Discussion
How to transform / unpivot columns to keep an attribute, value and date
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 |
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-Forum/ba-p/963216
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/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.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!!
7 Replies
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-Forum/ba-p/963216
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/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.Will do, thanks for the info. I'll close this and start a new topic
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 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!!Thank you shafiz_p, your code worked and gave me the desired results 🙂
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 ChangedTypev2
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
