Power Query sequence numbers

S

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
45
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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),
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
sadavies2906 said:
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"
Click to expand...
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

earthworm
  • Question
Exporting Data from Power Query into Multiple Sheet and merging steps into one.
Replies
3
Views
328
Matt Allington
M
tjdrake
  • Question
Excel VBA - Path to CSV file in Power Query
Replies
0
Views
403
tjdrake
tjdrake
tbablue
  • Question
Help required understanding how to implement NullSteps
Replies
0
Views
230
tbablue
tbablue
L
  • Question
Help win unpivot super slow query
2
Replies
15
Views
505
labello27
L
D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
270
Matt Allington
M

Forum statistics

Threads
1,214,641
Messages
6,120,693
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top