Hello,
thanks to everyone who participated! I loved to see so many variants of solutions with records from @borydobon, @Luan, @Rajesh, @elaf70. Many solutions were with multiple Table.AddColumn - and I am going to add 2 more to them to show that it is indeed not an advanced problem.
We started off normally in the Power Query Editor with an existing table. This time we are going for New Source on the Home tab and we select File > Text/CSV
Bring in the path to the CSV file and press open:
In this case we can trust what Power Query suggests and simply press OK:
All three columns have a data type text, which is fine for the first two, but we are going to change Column3. Right mouse click on it and select Change Type > Date:
Now we select on the Add Column tab Custom Column and call it Supplier # and bring in this if formula:
if [Column3]=null then [Column1] else null
We do the same again for Supplier Name and this time we use this formula:
if [Column3]=null then [Column2] else null
You have seen it’s twice the same simple condition which allows us to identify the info we need.
Three times is a charm so we do it once more for Invoice with a different but still simple condition:
if [Column2] <> “Utbetaining” then [Column2] else null
You probably understand that we want to fill down these three columns, but we also want to fill down Column1. To do that we right mouse click Column1 and select Replace Values…
Leave the first field empty and write in Replace With null:
Press CTRL and click on Column1, Supplier #, Supplier Name and Invoice and then right mouse click on one of them to select Fill > Down:
Click on the top right triangle on Column3 and deselect null to get rid of the rows with null in Column3:
That looks already good, but we need to replace the content in Column2. Right mouse click on it and select once more Replace Values…
Leave the first field empty again and write in Replace With Invoice:
You will see in the formula bar
= Table.ReplaceValue(#“Filtered Rows”,
“”,“Invoice”,Replacer.ReplaceText,{“Column2”})
and you replace
“” with
each [Invoice]
= Table.ReplaceValue(#“Filtered Rows”,
each [Invoice],“Invoice”,Replacer.ReplaceText,{“Column2”})
Every time the content in Column2 equals the content in column Invoice it will be replaced by the word “Invoice”.
Make a double click on the Column1and change the name to System #
Repeat for Column2 to change it to Type and then once mor for Column3 to name it Date. Done!
With adapted name steps the code would look like this:
let
Source = Csv.Document(File.Contents("C:\YourPath\12 Supplier Invoices.csv"),[Delimiter=";", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column3", type date}}),
#"Added Supplier #" = Table.AddColumn(#"Changed Type", "Supplier #", each if [Column3]=null then [Column1] else null),
#"Added Supplier" = Table.AddColumn(#"Added Supplier #", "Supplier Name", each if [Column3]=null then [Column2] else null),
#"Added Invoice" = Table.AddColumn(#"Added Supplier", "Invoice", each if [Column2] <> "Utbetaining" then [Column2] else null),
#"Replaced Value" = Table.ReplaceValue(#"Added Invoice","",null,Replacer.ReplaceValue,{"Column1"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1", "Supplier #", "Supplier Name", "Invoice"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column3] <> null)),
#"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [Invoice],"Invoice",Replacer.ReplaceText,{"Column2"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Utbetaining","Payment",Replacer.ReplaceText,{"Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value2",{{"Column1", "System #"}, {"Column2", "Type"}, {"Column3", "Date"}})
in
#"Renamed Columns"
That’s not at all complicated.
If you are open to another replacing logic for Column2 we can get rid of one replacing step. Right mouse click on Column2 and select Replace Values…
This time we leave both fields empty and just press OK.
We replace this formula
= Table.ReplaceValue(#“Filtered Rows”,“”,“”,Replacer.ReplaceValue,{“Column2”})
with
= Table.ReplaceValue(#“Filtered Rows”,each [Column2],each if [Column2] = “Utbetaining” then “Payment” else “Invoice”,Replacer.ReplaceText,{“Column2”})
This will replace each Utbetaining in Column2 with the word Payment and all other Column2 values with the word Invoice.
Then rename and you are done. Also not too complicated.
let
Source = Csv.Document(File.Contents("C:\YourPath\12 Supplier Invoices.csv"),[Delimiter=";", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column3", type date}}),
#"Added Supplier #" = Table.AddColumn(#"Changed Type", "Supplier #", each if [Column3]=null then [Column1] else null),
#"Added Supplier" = Table.AddColumn(#"Added Supplier #", "Supplier Name", each if [Column3]=null then [Column2] else null),
#"Added Invoice" = Table.AddColumn(#"Added Supplier", "Invoice", each if [Column2] <> "Utbetaining" then [Column2] else null),
#"Replaced Value" = Table.ReplaceValue(#"Added Invoice","",null,Replacer.ReplaceValue,{"Column1"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1", "Supplier #", "Supplier Name", "Invoice"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column3] <> null)),
#"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [Column2],each if [Column2] = "Utbetaining" then "Payment" else "Invoice",Replacer.ReplaceText,{"Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Column1", "System #"}, {"Column2", "Type"}, {"Column3", "Date"}})
in
#"Renamed Columns"
Try them both and make sure to check out the other approaches above! => Hope you learn something!
Big thanks to everyone who has blurred or hidden the details! It allows readers to develop their own ideas without being influenced - and it’s easy enough to check them afterwards.
