add a serial number

ExcelStarter

New member
Joined
Jan 23, 2018
Messages
27
Reaction score
0
Points
0
Excel Version(s)
2016
Snap35.jpg

Is it possible to add a serial number as shown?
 

Attachments

  • sample.xlsx
    11.8 KB · Views: 21
Last edited:
By UI only
Code:
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.
Code:
#"Added Index" = Table.AddIndexColumn(Source, "Indeks", 0, List.Count(Table.ColumnNames(Source)))
 
Thank you! Bill Szysz

Even if it is a little hard to understand, can you use M to make it simpler?
 
Sorry.I was not expressive enough.

Even if it's not UI....Can you edit M to make it simpler?
 
Why does it need to be simpler? What is your perceived benefit? Does it do what you want?
 
This is slightly smaller, but there is no simpler way I can think of
Code:
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"
 
Last edited:
Thank you! horseyride

* Sorry for the inconvenience.

It does not matter.


Please let me know if possible.
 
Back
Top