add a serial number

[ExcelStarter]

Is it possible to add a serial number as shown?

 

[Bill Szysz]

By UI only

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Indeks", 0, 7),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Indeks"}, "Atrybut", "Wartość"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Atrybut", "Atrybut — kopia"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Atrybut — kopia", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Atrybut — kopia.1", "Atrybut — kopia.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Atrybut — kopia.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Number", each [Indeks]+[#"Atrybut — kopia.2"]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Number", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Number", "Wartość"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"NewValue"),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Indeks", "Atrybut", "NewValue"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Atrybut]), "Atrybut", "NewValue"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Indeks"})
in
    #"Removed Columns"

iIf you want to make it dynamic (regardless of the number of columns) then change #"Added Index" step to.

#"Added Index" = Table.AddIndexColumn(Source, "Indeks", 0, List.Count(Table.ColumnNames(Source)))

 

[ExcelStarter]

Thank you! Bill Szysz

Even if it is a little hard to understand, can you use M to make it simpler?

 

[horseyride]

that is M

 

[ExcelStarter]

Sorry.I was not expressive enough.

Even if it's not UI....Can you edit M to make it simpler?

 

[AliGW]

Why does it need to be simpler? What is your perceived benefit? Does it do what you want?

 

[horseyride]

This is slightly smaller, but there is no simpler way I can think of

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Added Index1" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each Text.From([Index]) & " " & Text.From([Value]) ),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Index2", each Number.RoundUp([Index]/List.Count(Table.ColumnNames(Source)))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Value", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index2"})
in #"Removed Columns1"

 

[ExcelStarter]

Thank you! horseyride

* Sorry for the inconvenience.

It does not matter.


Please let me know if possible.

 

[AliGW]

See post #7.