Power Query sequence numbers

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
44
Hi, I have the following Power Query running that eventually creates a workbook table with a column "WTL_SEQ_NEW"

let
Source = Excel.Workbook(File.Contents("N:\ManufacturingOps\Spares Overdue Weekly Report\2020 WORK TO LISTS\DATA FILES\SDD_ESC_Resource Requirement Report UK V2.xls"), null, true),
#"ESC Build" = Source{[Name="ESC Build"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"ESC Build", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start Date", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Hours", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Location] = "POST VENDOR"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Start Date", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,"-",Replacer.ReplaceValue,{"Destination"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"-",Replacer.ReplaceValue,{"Po Number"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "WTL_SEQ_NEW", each Text.End([Week],2)),
#"Sorted Rows1" = Table.Sort(#"Added Custom",{{"WTL_SEQ_NEW", Order.Ascending}, {"Assembly Item", Order.Ascending}, {"Job Number", Order.Ascending}})
in
#"Sorted Rows1"

The result is shown below as "CURRENT" but i would like to automatically create a sequence similar to "REQUIRED" where it increments by +1

1593678528907.png


Regards
Stephen
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,939
Office Version
  1. 2019
Platform
  1. Windows
Using your current Column as a stand alone table in my example

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Range(Source[Current],[Index]-1,1)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Custom", "Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Current"}, {{"Data", each _, type table [Current=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Current", "Index"}, {"Current.1", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom", each [Current.1]+[Index]/10),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Data", "Current.1", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Required"}})
in
    #"Renamed Columns"

Book3
AB
1CurrentRequired
22727.1
32727.2
42727.3
52727.4
62727.5
72828.1
82828.2
92828.3
102828.4
112828.5
122929.1
132929.2
142929.3
152929.4
162929.5
Sheet2
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
or another approach
RawResult
2727.01
2727.02
2727.03
2727.04
2727.05
2727.06
2727.07
2727.08
2727.09
2828.01
2828.02
2828.03
2828.04
2828.05
2828.06
2929.01
2929.02
2929.03
2929.04
2929.05
2929.06
2929.07

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Raw"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each [Count][Raw]),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Count = Table.AddColumn(Extract, "Div", each List.Count(Text.Split([List],","))),
    Number = Table.AddColumn(Count, "Number", each {1..[Div]}),
    Expand = Table.ExpandListColumn(Number, "Number"),
    TypeText = Table.TransformColumnTypes(Expand,{{"Number", type text}}),
    PadStart = Table.AddColumn(TypeText, "TPS", each Text.PadStart([Number], 2, "0")),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(PadStart, {{"Raw", type text}}, "en-GB"),{"Raw", "TPS"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Result"),
    TSC = Table.SelectColumns(Merge,{"Result"}),
    Result = Table.TransformColumnTypes(TSC,{{"Result", type number}})
in
    Result
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,939
Office Version
  1. 2019
Platform
  1. Windows
Have edited above to the following:
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Current"}, {{"Data", each _, type table [Current=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 0, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Current", "Index"}, {"Current.1", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom", each [Current.1]+[Index]/10),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Data", "Current.1", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Required"}})
in
    #"Renamed Columns"

Book4
AB
1CurrentRequired
22727
32727.1
42727.2
52727.3
62727.4
72828
82828.1
92828.2
102828.3
112828.4
122929
132929.1
142929.2
152929.3
162929.4
Sheet3
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,939
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Noticed an error in my code.

Change this line
Rich (BB code):
#"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom", each [Current.1]+[Index]/10),

to

Rich (BB code):
#"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom", each [Current.1]+[Index]/100),
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
with your M it should(?) work
Rich (BB code):
let
Source = Excel.Workbook(File.Contents("N:\ManufacturingOps\Spares Overdue Weekly Report\2020 WORK TO LISTS\DATA FILES\SDD_ESC_Resource Requirement Report UK V2.xls"), null, true),
#"ESC Build" = Source{[Name="ESC Build"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"ESC Build", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start Date", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Hours", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Location] = "POST VENDOR"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Start Date", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,"-",Replacer.ReplaceValue,{"Destination"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"-",Replacer.ReplaceValue,{"Po Number"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "WTL_SEQ_NEW", each Text.End([Week],2)),
#"Sorted Rows1" = Table.Sort(#"Added Custom",{{"WTL_SEQ_NEW", Order.Ascending}, {"Assembly Item", Order.Ascending}, {"Job Number", Order.Ascending}}),
    Group = Table.Group(#"Sorted Rows1", {"WTL_SEQ_NEW"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each [Count][WTL_SEQ_NEW]),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Count = Table.AddColumn(Extract, "Div", each List.Count(Text.Split([List],","))),
    Number = Table.AddColumn(Count, "Number", each {1..[Div]}),
    Expand = Table.ExpandListColumn(Number, "Number"),
    TypeText = Table.TransformColumnTypes(Expand,{{"Number", type text}}),
    PadStart = Table.AddColumn(TypeText, "TPS", each Text.PadStart([Number], 2, "0")),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(PadStart, {{"WTL_SEQ_NEW", type text}}, "en-GB"),{"WTL_SEQ_NEW", "TPS"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Result"),
    TSC = Table.SelectColumns(Merge,{"Result"}),
    Result = Table.TransformColumnTypes(TSC,{{"Result", type number}})
in
    Result
 

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
44

ADVERTISEMENT

Thank you both, how would I apply either of these without making it a new query as you have both shown?

There are about another 10 columns on my original output that i haven't shown, so i really need the numbering sequence to be applied after my "Sorted Rows1"
 

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
44
Thank you sandy666, that was exactly what i was just asking in the above post. I will give it a go now and see what i get
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
Thank you both, how would I apply either of these without making it a new query as you have both shown?

There are about another 10 columns on my original output that i haven't shown, so i really need the numbering sequence to be applied after my "Sorted Rows1"
post#1 contain single column example so solution is tailored to your example and description
best way is post representative structure via XL2BB because we cannot test your M (no access to the file in the source path)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,634
Members
410,861
Latest member
Victor96
Top