Power Query : Formula To Replace/Fill Sequence Number With Text [Adjacent Cell]

muhammad susanto

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,091
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all..

the following data set is problem, how to filling or insert or replace sequence number (1,2,3,4,5) or (1,2,3,4,5,6) with text name of car (red font)
here this sample data set
original data setdesired result
Column1Column1
Mercy
SeriesSeries
1Mercy
2Mercy
3Mercy
4Mercy
5Mercy
Corolla
SeriesSeries
1Corolla
2Corolla
3Corolla
4Corolla
5Corolla
6Corolla
BMW
SeriesSeries
1BMW
2BMW
3BMW
4BMW
5BMW
etc..


the get data using power bi, with my source data from google sheet
docs.google.com

sample capkin 2022 by sst

docs.google.com docs.google.com
with sheet name "new adku"
my target only in column 1,..
anyone help me, greatly appreciated..

susan
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
There is probably a neater way to do this but here is my go
Book1
ABC
1original data setdesired resultPQ List
2Mercy
3SeriesSeriesSeries
41MercyMercy
52MercyMercy
63MercyMercy
74MercyMercy
85MercyMercy
9
10
11Corolla
12SeriesSeriesSeries
131CorollaCorolla
142CorollaCorolla
153CorollaCorolla
164CorollaCorolla
175CorollaCorolla
186CorollaCorolla
19
20
21BMW
22SeriesSeriesSeries
231BMWBMW
242BMWBMW
253BMWBMW
264BMWBMW
275BMWBMW
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"original data set", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if[original data set]="Series" or [original data set]+[Index]>=1  then null else [original data set]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", 1}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if[Custom]=1 then[original data set] else [Custom]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Custom.2", each if[original data set]=[Custom.1] then "" else [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if[original data set]= "Series" or [original data set]=null then[original data set] else[Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"original data set", "Index", "Custom", "Custom.1", "Custom.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.3", "PQ List"}})
in
    #"Renamed Columns"
 
Upvote 0
hi Kerryx..
i have to do with your formula but not work..
i modified the code
Power Query: 
let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/1TO4mggqdC3JzfwD8iKHYEGQUMMkpWhu5O3QyS0vDS-w"),
    #"new adku_Table" = Source{[name="new adku",ItemKind="Table"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"new adku_Table",2),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pertama", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if[pertama]="No" or [pertama]+[Index]>=1  then null else [pertama]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", 1}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if[Custom]=1 then[pertama] else [Custom]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Custom.2", each if[pertama]=[Custom.1] then "" else [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if[pertama]= "No" or [pertama]=null then[pertama] else[Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"pertama", "Index", "Custom", "Custom.1", "Custom.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.3", "PQ List"}})
in
    #"Renamed Columns"

show exprresion error, The column "pertama' of the table not found
i upload orginal into PQ

how yo fix this problem?
 

Attachments

  • pq not work.jpg
    pq not work.jpg
    45.2 KB · Views: 7
Upvote 0
Ok when reviewing the sequence i see that the column you renamed reverted back to previous name "Column1" that is why the error column "pertama" not found
I could not open your file but hope i understood you correctly in that this is what you are looking for
Book1
ABC
1PQ ListpertamaColumn2
2IKU TINGKAT KESESUAIAN PENGGUNAAN BMN DENGAN SBSK (1)
3NoNoUnit
4IKU TINGKAT KESESUAIAN PENGGUNAAN BMN DENGAN SBSK (1)1KPKNL Padang
5IKU TINGKAT KESESUAIAN PENGGUNAAN BMN DENGAN SBSK (1)2KPKNL Bukittinggi
6IKU TINGKAT KESESUAIAN PENGGUNAAN BMN DENGAN SBSK (1)3KPKNL Pekanbaru
7IKU TINGKAT KESESUAIAN PENGGUNAAN BMN DENGAN SBSK (1)4KPKNL Batam
8IKU TINGKAT KESESUAIAN PENGGUNAAN BMN DENGAN SBSK (1)5KPKNL Dumai
9Kanwil DJKN RSK
Table2


from this
Book1
ABCDEFGHIJK
1Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11
2IKU TINGKAT KESESUAIAN PENGGUNAAN BMN DENGAN SBSK (1)
3NoUnitTarget Th 2022Target s.d. September 2022Realisasi s.d. September 2022Indeks sd Triwulan IIRealisasi s.d. 30 September 2022Indeks Capaian Proyeksi s.d. 30 September 2022
41KPKNL Padang65%55,00%71,54%130,07%0,00%
52KPKNL Bukittinggi65%55,00%87,11%158,38%87,11%134,02%
63KPKNL Pekanbaru65%55,00%81,06%147,38%81,06%124,71%
74KPKNL Batam65%55,00%73,70%134,00%73,70%113,38%
85KPKNL Dumai65%55,00%71,14%129,35%0,00%
9Kanwil DJKN RSK65%55,00%71,59%130,16%0,00%
10
11PNBP ASET-BMN (2)
12NoUnitRaw Data Tahun 2022Raw Data Target Q2 (30%)Raw Data target s.d. September 2022Realisasi s.d. September 2022% Realisasi s.d.September 2022Indeks Capaian s.d. September 2022Indeks Capaian TahunanProyeksi Realisasi s.d. 30 September 2022Indeks Capaian Proyeksi Realisasi s.d. 30 September 2022
131KPKNL Padang4.500.000.000,002.700.000.000,002.700.000.000,005.935.031.202,00131,89%219,82%131,89%6.429.617.135,50142,88%
142KPKNL Bukittinggi4.000.000.000,002.400.000.000,002.400.000.000,002.533.590.989,0063,34%105,57%63,34%Rp2.533.590.98963,34%
153KPKNL Pekanbaru8.600.000.000,005.160.000.000,005.160.000.000,0010.429.354.471,00121,27%202,12%121,27%11.298.467.343,58131,38%
164KPKNL Batam142.783.200.000,0085.669.920.000,0085.669.920.000,00205.965.559.729,00144,25%240,42%144,25%223.129.356.373,08156,27%
175KPKNL Dumai5.250.000.000,003.150.000.000,003.150.000.000,002.739.493.316,0052,18%86,97%52,18%2.967.784.425,6756,53%
18TOTAL KANWIL165.133.200.000,00Rp 99.079.920.000Rp 99.079.920.000Rp 227.603.029.707137,83%229,72%137,83%Rp 246.358.816.267149,19%
19
20PNBP PIUTANG NEGARA (3)
21NoUnitRaw Data Tahun 2022Raw Data Target Q2 (30%)Raw Data target s.d. September 2022Realisasi s.d. September 2022% Realisasi s.d.September 2022Indeks Capaian s.d. September 2022Indeks Capaian TahunanProyeksi Realisasi s.d. 30 September 2022Indeks Capaian Proyeksi s.d. 30 September 2022
221KPKNL Padang7.193.189,004.315.913,40Rp 4.315.9137.308.793,00101,61%169,35%101,61%7.917.859,08110,07%
232KPKNL Bukittinggi7.200.000,004.320.000,00Rp 4.320.0003.220.835,0044,73%74,56%44,73%Rp3.489.23848,46%
243KPKNL Pekanbaru30.000.000,0018.000.000,00Rp 18.000.00071.925.865,00239,75%399,59%239,75%77.919.687,08259,73%
254KPKNL Batam20.000.000,0012.000.000,00Rp 12.000.00041.271.047,00206,36%343,93%206,36%44.710.300,92223,55%
265KPKNL Dumai10.173.410,006.104.046,00Rp 6.104.04625.471.179,00250,37%417,28%250,37%27.593.777,25271,23%
27TOTAL KANWIL74.566.599,0044.739.959,40Rp 44.739.959Rp 149.197.719200,09%333,48%200,09%Rp 161.630.862216,76%
Sheet2


here is the adjusted PQ code- i simply moved the column rename to Pertama before doing any of the other transformations
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "pertama"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"pertama", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if[pertama]="No" or [pertama]+[Index]>=1  then null else [pertama]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", 1}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if[Custom]=1 then[pertama] else [Custom]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Custom.2", each if[pertama]=[Custom.1] then "" else [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if[pertama]= "No" or [pertama]=null then[pertama] else[Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{ "Index", "Custom", "Custom.1", "Custom.2"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom.3", "PQ List"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"PQ List", "pertama", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"})
in
    #"Reordered Columns"
 
Upvote 0
hi Kerryx..
i have do with step:
1. Rename Column1 with 'pertama' with manually (double click in text then type "pertama"
2. then copy paste your formula into PQ Editor
here this code
Power Query: 
let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/1TO4mggqdC3JzfwD8iKHYEGQUMMkpWhu5O3QyS0vDS-w"),
    #"new adku_Table" = Source{[name="new adku",ItemKind="Table"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "pertama"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"pertama", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if[pertama]="No" or [pertama]+[Index]>=1  then null else [pertama]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", 1}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if[Custom]=1 then[pertama] else [Custom]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Custom.2", each if[pertama]=[Custom.1] then "" else [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if[pertama]= "No" or [pertama]=null then[pertama] else[Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{ "Index", "Custom", "Custom.1", "Custom.2"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom.3", "PQ List"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"PQ List", "pertama", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"})
in
    #"Reordered Columns"
i have write full code for google sheet as source but still error show message
Expression.Error: The column 'Column1' of the table wasn't found.
how to fix this ? please, give me easy step
 
Upvote 0
Sorry don't use google sheets I can only suggest you go through the query steps to see where the issue is arising as i cannot replicate the issue. Maybe somone else here can offer a suggestion.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Power Query Help to Calculate Stock Splits
Replies
0
Views
162
rasinc
R
K
  • Solved
Excel Power Query: Custom Text String Formula
Replies
8
Views
2K
smozgur
smozgur
T
  • Solved
Power Query: pivot column with text as value field
Replies
2
Views
1K
tombamber
T
D
  • Question Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
2K
Matt Allington
M
P
  • Question Question
Numbering Columns with dates as names using Power Query
Replies
6
Views
732
RoryA
RoryA

Forum statistics

Threads
1,223,996
Messages
6,175,857
Members
452,676
Latest member
woodyp

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