Forum Discussion

Cream_17's avatar
Cream_17
Frequent Visitor
2 years ago
Solved

Pivot the table

Hi guys,   I really need your help   I want to change the table (table one) from this one   Column1 1 A B 2 C 3 D E 4 F G 5 H   to this one (table...
Need Help
Show and Tell
  • AlienSx's avatar
    AlienSx
    2 years ago
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(
        Source,
        "Column1", 
        {"x", (x) => x[Column1]},
        GroupKind.Local, 
        (s, c) => Number.From(c is number)
    ),
    z = Table.FromList(group[x], (x) => x, {"No", "Manchester", "Arsenal"})
in
    z
Anonymous's avatar
Anonymous
Not applicable
2 years ago

You can extract the text into a new column using if each List.Contains({0..9}, [Column1])then [Column1] else null) and  then fill down that column. Then remove  any rows where Column1 = number column. Then group by filldown column, and extract to new columns. I can't write out the code right now, but logically, thats how I would do it.

 

--Nate

  • Cream_17's avatar
    Cream_17
    Frequent Visitor
    2 years ago

    Dear Watkinnc, thanks for your reply

    I have been here

     


    and my current M code as below:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is(Number.From([Column1]), type number) then [Column1] else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"Custom"}),
    #"AddedCustom" = Table.AddColumn(#"Filled Down", "IsNumeric", each if Value.Is(Value.FromText([Column1]), type number) then "Numeric" else "Non-numeric"),
    #"FilteredRows" = Table.SelectRows(#"AddedCustom", each ([IsNumeric] = "Non-numeric")),
    #"RemovedCustom" = Table.RemoveColumns(FilteredRows,{"IsNumeric"})
    in
    RemovedCustom

    I still don`t understand with : "Then group by filldown column, and extract to new columns", to return the final table I desired.