Forum Discussion

ytulpar's avatar
ytulpar
New Member
2 years ago
Solved

Transform data to create matrix table

My source data looks like this: Full Name Internal Work Experience 1:Job Title Internal Work Experience 1:From Internal Work Experience 1:To Internal Work Experience 1:Department Internal Wo...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi,

     

    Unpivot, Split by ":", Pivot

     

    let
    Source = YourSource,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Full Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value")
    in
    #"Pivoted Column"

    Stéphane 

slorin's avatar
slorin
Super User
2 years ago

Hi,

 

Unpivot, Split by ":", Pivot

 

let
Source = YourSource,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Full Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"

Stéphane