Results 1 to 9 of 9

Thread: add a serial number

  1. #1
    Seeker ExcelStarter's Avatar
    Join Date
    Jan 2018
    Posts
    19
    Articles
    0
    Excel Version
    2016

    add a serial number



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Click image for larger version. 

Name:	Snap35.jpg 
Views:	29 
Size:	69.5 KB 
ID:	8979

    Is it possible to add a serial number as shown?
    Attached Files Attached Files
    Last edited by ExcelStarter; 2019-03-20 at 05:27 AM.

  2. #2
    Acolyte Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    93
    Articles
    0
    Excel Version
    Excel 365
    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)))

  3. #3
    Seeker ExcelStarter's Avatar
    Join Date
    Jan 2018
    Posts
    19
    Articles
    0
    Excel Version
    2016
    Thank you! Bill Szysz

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

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    72
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    that is M

  5. #5
    Seeker ExcelStarter's Avatar
    Join Date
    Jan 2018
    Posts
    19
    Articles
    0
    Excel Version
    2016
    Sorry.I was not expressive enough.

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

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,168
    Articles
    0
    Excel Version
    Office 365 Subscription
    Why does it need to be simpler? What is your perceived benefit? Does it do what you want?
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    72
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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 by horseyride; 2019-03-20 at 03:09 PM.

  8. #8
    Seeker ExcelStarter's Avatar
    Join Date
    Jan 2018
    Posts
    19
    Articles
    0
    Excel Version
    2016
    Thank you! horseyride

    * Sorry for the inconvenience.

    It does not matter.


    Please let me know if possible.

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,168
    Articles
    0
    Excel Version
    Office 365 Subscription
    See post #7.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •