Power Query - program a column to automatically fill values in

[anilomevina]

Hello and good afternoon,
I was wondering if there is a way to automatically fill column D with AAAABBBBCCCCDDDD(and so on) as part of a Power Query.
Thank you in advance.

 

[sandy666]

may I ask you about link to the shared result (from txt files you posted above) excel file?
to me there is a big mess in these files

 

[sandy666]

is that what you want? (or something like that)

 

[anilomevina]

is that what you want? (or something like that)
View attachment 3510

Sorry, I have only just seen it, Yes!! This is perfect!! May I ask how did you manage that?

 

[sandy666]

or even like this?

 

[anilomevina]

or even like this?
View attachment 3512

Yes I have already done this separation, I just could not manage to do your 'custom' column

 

[sandy666]

// 17 1-A
// change path to file above

let
    Source = Csv.Document(File.Contents("D:\test\anastasia\17.1-A.txt"),[Delimiter="#(lf)", Columns=1, Encoding=1200, QuoteStyle=QuoteStyle.Csv]),
    #"Replaced Value" = Table.ReplaceValue(Source,"#(tab)","",Replacer.ReplaceText,{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Column1] <> "" and [Column1] <> "111 Tttttttttt Pppphttp://www.aaaa.com" and [Column1] <> "actualnominaldeviation" and [Column1] <> "CAM2 Measure Inspection Report1/1" and [Column1] <> "Date11/21/2019" and [Column1] <> "Ff 333333sssssss@ssss.com" and [Column1] <> "FFFF FFFFFFFFFFF FFF." and [Column1] <> "Llll Mddd(444) 444-4444" and [Column1] <> "OperatorBB" and [Column1] <> "Part Name12345" and [Column1] <> "Program Name123 F1234" and [Column1] <> "Readings:1." and [Column1] <> "Session Information" and [Column1] <> "Session Name17.1-A AFT 123lbs" and [Column1] <> "USB22 Ddd 2222 44:44 Pp")),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "Point") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Point") and not Text.Contains([Column1], "3D")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Custom", "Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Reordered Columns","x","x@",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","y","y@",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","z","z@",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","in","@",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Column1", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.5"})
in
    #"Removed Columns"

then set data type for each column if necessary

 

[sandy666]

shorter version

// 17 1-A
let
    Source = Csv.Document(File.Contents("D:\test\anastasia\17.1-A.txt"),[Delimiter="#(lf)", Columns=1, Encoding=1200, QuoteStyle=QuoteStyle.Csv]),
    #"Replaced Value" = Table.ReplaceValue(Source,"#(tab)","",Replacer.ReplaceText,{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Column1] <> "" and [Column1] <> "111 Tttttttttt Pppphttp://www.aaaa.com" and [Column1] <> "actualnominaldeviation" and [Column1] <> "CAM2 Measure Inspection Report1/1" and [Column1] <> "Date11/21/2019" and [Column1] <> "Ff 333333sssssss@ssss.com" and [Column1] <> "FFFF FFFFFFFFFFF FFF." and [Column1] <> "Llll Mddd(444) 444-4444" and [Column1] <> "OperatorBB" and [Column1] <> "Part Name12345" and [Column1] <> "Program Name123 F1234" and [Column1] <> "Readings:1." and [Column1] <> "Session Information" and [Column1] <> "Session Name17.1-A AFT 123lbs" and [Column1] <> "USB22 Ddd 2222 44:44 Pp")),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "Point") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Point") and not Text.Contains([Column1], "3D")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Custom", "Column1"}),
    #"Split Column by Position" = Table.SplitColumn(#"Reordered Columns", "Column1", Splitter.SplitTextByPositions({0, 8}, false), {"Column1.1", "Column1.2"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Split Column by Position","in","@",Replacer.ReplaceText,{"Column1.2"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Column1.2", Splitter.SplitTextByAnyDelimiter({"@"}, QuoteStyle.Csv)),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.2.4"})
in
    #"Removed Columns1"

 

[anilomevina]

shorter version

// 17 1-A
let
    Source = Csv.Document(File.Contents("D:\test\anastasia\17.1-A.txt"),[Delimiter="#(lf)", Columns=1, Encoding=1200, QuoteStyle=QuoteStyle.Csv]),
    #"Replaced Value" = Table.ReplaceValue(Source,"#(tab)","",Replacer.ReplaceText,{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Column1] <> "" and [Column1] <> "111 Tttttttttt Pppphttp://www.aaaa.com" and [Column1] <> "actualnominaldeviation" and [Column1] <> "CAM2 Measure Inspection Report1/1" and [Column1] <> "Date11/21/2019" and [Column1] <> "Ff 333333sssssss@ssss.com" and [Column1] <> "FFFF FFFFFFFFFFF FFF." and [Column1] <> "Llll Mddd(444) 444-4444" and [Column1] <> "OperatorBB" and [Column1] <> "Part Name12345" and [Column1] <> "Program Name123 F1234" and [Column1] <> "Readings:1." and [Column1] <> "Session Information" and [Column1] <> "Session Name17.1-A AFT 123lbs" and [Column1] <> "USB22 Ddd 2222 44:44 Pp")),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "Point") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Point") and not Text.Contains([Column1], "3D")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Custom", "Column1"}),
    #"Split Column by Position" = Table.SplitColumn(#"Reordered Columns", "Column1", Splitter.SplitTextByPositions({0, 8}, false), {"Column1.1", "Column1.2"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Split Column by Position","in","@",Replacer.ReplaceText,{"Column1.2"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Column1.2", Splitter.SplitTextByAnyDelimiter({"@"}, QuoteStyle.Csv)),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.2.4"})
in
    #"Removed Columns1"

Thank you so much!!! I shall try it tomorrow because I don't have access to my computer at the moment.
I really really appreciate your help

 

[sandy666]

sure,
also you can try this ?

// 17 1-A
let
    Source = Csv.Document(File.Contents("D:\test\anastasia\17.1-A.txt"),[Delimiter="#(lf)", Columns=1, Encoding=1200, QuoteStyle=QuoteStyle.Csv]),
    #"Replaced Value" = Table.ReplaceValue(Source,"#(tab)","",Replacer.ReplaceText,{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Column1] <> "" and [Column1] <> "111 Tttttttttt Pppphttp://www.aaaa.com" and [Column1] <> "actualnominaldeviation" and [Column1] <> "CAM2 Measure Inspection Report1/1" and [Column1] <> "Date11/21/2019" and [Column1] <> "Ff 333333sssssss@ssss.com" and [Column1] <> "FFFF FFFFFFFFFFF FFF." and [Column1] <> "Llll Mddd(444) 444-4444" and [Column1] <> "OperatorBB" and [Column1] <> "Part Name12345" and [Column1] <> "Program Name123 F1234" and [Column1] <> "Readings:1." and not Text.Contains([Column1], "Session") and [Column1] <> "USB22 Ddd 2222 44:44 Pp" and not Text.Contains([Column1], "3D")),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "Point") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Point")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Custom", "Column1"}),
    #"Split Column by Position" = Table.SplitColumn(#"Reordered Columns", "Column1", Splitter.SplitTextByPositions({0, 8}, false), {"Column1.1", "Column1.2"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Position", {{"Column1.2", each Text.BeforeDelimiter(_, "in", {0, RelativePosition.FromEnd}), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text Before Delimiter", "Column1.2", Splitter.SplitTextByAnyDelimiter({"in"}, QuoteStyle.Csv))
in
    #"Split Column by Delimiter"

code is not optimised as I want but I am a bit tired so the rest is up to you

 

[anilomevina]

sure,
also you can try this ?

// 17 1-A
let
    Source = Csv.Document(File.Contents("D:\test\anastasia\17.1-A.txt"),[Delimiter="#(lf)", Columns=1, Encoding=1200, QuoteStyle=QuoteStyle.Csv]),
    #"Replaced Value" = Table.ReplaceValue(Source,"#(tab)","",Replacer.ReplaceText,{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Column1] <> "" and [Column1] <> "111 Tttttttttt Pppphttp://www.aaaa.com" and [Column1] <> "actualnominaldeviation" and [Column1] <> "CAM2 Measure Inspection Report1/1" and [Column1] <> "Date11/21/2019" and [Column1] <> "Ff 333333sssssss@ssss.com" and [Column1] <> "FFFF FFFFFFFFFFF FFF." and [Column1] <> "Llll Mddd(444) 444-4444" and [Column1] <> "OperatorBB" and [Column1] <> "Part Name12345" and [Column1] <> "Program Name123 F1234" and [Column1] <> "Readings:1." and not Text.Contains([Column1], "Session") and [Column1] <> "USB22 Ddd 2222 44:44 Pp" and not Text.Contains([Column1], "3D")),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "Point") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Point")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Custom", "Column1"}),
    #"Split Column by Position" = Table.SplitColumn(#"Reordered Columns", "Column1", Splitter.SplitTextByPositions({0, 8}, false), {"Column1.1", "Column1.2"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Position", {{"Column1.2", each Text.BeforeDelimiter(_, "in", {0, RelativePosition.FromEnd}), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text Before Delimiter", "Column1.2", Splitter.SplitTextByAnyDelimiter({"in"}, QuoteStyle.Csv))
in
    #"Split Column by Delimiter"

code is not optimised as I want but I am a bit tired so the rest is up to you

Thank you so much, I really do appriciate it! but will it work with a folder of files? I have tried changing the first line to folder.files rather than document and also change the path but it come up with an error message