Forum Discussion

freddy21's avatar
freddy21
Frequent Visitor
10 months ago
Solved

Table Transformation

Hello everyone,   I have the following problem: I want to display the following table, which currently looks like this:   I want this: Id   Label1   Label2   Label3   Labe...
  • jgeddes's avatar
    10 months ago

    Add an Index column starting at 1 and incrementing by 1.

    Replace the values in the Index column, adding the 'Label' prefix.

    Pivot the transformed Index column. Use the 'labels' column as the Values column with no aggregation.

    Complete example code.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemaqUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"labels", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
        #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index], each "Label"&Number.ToText([Index]),Replacer.ReplaceValue,{"Index"}),
        #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Index]), "Index", "labels")
    in
        #"Pivoted Column"

     

  • ronrsnfld's avatar
    10 months ago

    With more than one ID, you can 

    • Group by ID
    • Create a list of Field Names dependent on the number of rows in the grouped table.
    • Create a list of records from the label values and the field names
    • Expand the records into new rows
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemYqRNrQ0MjYxNTMHKLXUiEjPysRU8bQwFAhLb+ouCQxJ0cpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"labels", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
            {"Row", (t)=> 
                [a=List.Repeat({"Label"}, Table.RowCount(t)),
                 b=List.Transform(List.Numbers(1, List.Count(a)), each Text.From(_)),
                 c=List.Zip({a,b}),
                 d=List.Transform(c, each Text.Combine(_, " ")),
                 e=Record.FromList(t[labels],d)][e]}}),
        
        #"Field Names" = List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[Row], each Record.FieldNames(_)))),
        #"Expanded Row" = Table.ExpandRecordColumn(#"Grouped Rows", "Row", #"Field Names")
    in
        #"Expanded Row"