Forum Discussion

lukedaly01's avatar
lukedaly01
Regular Visitor
2 years ago
Solved

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 ...
Need Help
  • foodd's avatar
    foodd
    2 years ago

    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.

  • shafiz_p's avatar
    shafiz_p
    2 years ago

    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!!

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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