Power BI - importing conditional clauses

[Paul0702]

Hello - I was wondering if it possible to import conditional clauses into an already existing Power BI table. we have multiple vendors and daily transactions that need cleaning up to make them look more uniform. like we do not need invoice numbers or posting dates from our systems when pulling the raw data into the power BI table. is there a way if I were to setup a separate spreadsheet with the formatting/clauses that I need for it to work? i already have some clauses in place but to manually do over 100 clauses would take forever. thank you for any help and advice. please let me know if more information is needed and I can hopefully help elaborate further.

 

[GraH]

You have Table.SelectColumns() function in Power Query.
You could have a query in PBI that contains a list with the columns needed. And in the Table.SelectColumns() step of the result query, you can refer to this list.
Assunign this data "sys_export"

Some date	Value	sys posting date	Client	Invoice Number	Tax regime	Product item
9/01/2025	500	25/01/2025	ACME	20250126-00001	A	ITM-CRD-5478
10/01/2025	742	25/01/2025	MYC°	20250126-00002	A	ITM-BKE-3651
11/01/2025	349	25/01/2025	ACE	20250126-00003	B	ITM-KNF-9999

Query with list of column names: col_to_select

[RANGE=rs:4|cs:7|v:table|w:Book1|cls:xl2bb-210|s:Sheet1|tw:492][XR][XD=ch:0|w:67|cls:h][/XD][XD=ch:0|w:48|cls:h][/XD][XD=ch:0|w:93|cls:h][/XD][XD=ch:0|w:48|cls:h][/XD][XD=ch:0|w:92|cls:h][/XD][XD=ch:0|w:68|cls:h][/XD][XD=ch:0|w:77|cls:h][/XD][/XR][XR][XD=h:l|fw:b|bc:156082|c:FFFFFF|ch:15|cls:bl bt bb]Some date[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Value[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]sys posting date[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Client[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Invoice Number[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Tax regime[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt br bb]Product item[/XD][/XR][XR][XD=bc:C0E6F5|ch:15|cls:bl bt bb|tx:45666|nf:m/d/yyyy]9/01/2025[/XD][XD=bc:C0E6F5|cls:bt bb]500[/XD][XD=bc:C0E6F5|cls:bt bb|tx:45682|nf:m/d/yyyy]25/01/2025[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]ACME[/XD][XD=bc:C0E6F5|cls:bt bb]20250126-00001[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]A[/XD][XD=h:l|bc:C0E6F5|cls:bt br bb]ITM-CRD-5478[/XD][/XR][XR][XD=ch:15|cls:bl bt bb|tx:45667|nf:m/d/yyyy]10/01/2025[/XD][XD=cls:bt bb]742[/XD][XD=cls:bt bb|tx:45682|nf:m/d/yyyy]25/01/2025[/XD][XD=h:l|cls:bt bb]MYC°[/XD][XD=cls:bt bb]20250126-00002[/XD][XD=h:l|cls:bt bb]A[/XD][XD=h:l|cls:bt br bb]ITM-BKE-3651[/XD][/XR][XR][XD=bc:C0E6F5|ch:15|cls:bl bt bb|tx:45668|nf:m/d/yyyy]11/01/2025[/XD][XD=bc:C0E6F5|cls:bt bb]349[/XD][XD=bc:C0E6F5|cls:bt bb|tx:45682|nf:m/d/yyyy]25/01/2025[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]ACE[/XD][XD=bc:C0E6F5|cls:bt bb]20250126-00003[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]B[/XD][XD=h:l|bc:C0E6F5|cls:bt br bb]ITM-KNF-9999[/XD][/XR][/RANGE]

Queyr with applied selection referring to that list.

let
    Source = Excel.CurrentWorkbook(){[Name="sys_export"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Some date", type datetime}, {"Value", Int64.Type}, {"sys posting date", type datetime}, {"Client", type text}, {"Invoice Number", type text}, {"Tax regime", type text}, {"Product item", type text}}),
    Custom1 = Table.SelectColumns(#"Changed Type", col_to_select)
in
    Custom1

If with formatting , you mean data types, the you can create a list of lists. Where the inner list contains 2 items, the column name and the data type.

//cols_to_select
let
    Source = {
                 {"Some date", Date.Type}
                ,{"Value", Number.Type}
                //,"sys posting date"
                ,{"Client", Text.Type}
                //,"Invoice Number"
                ,{"Tax regime", Text.Type}
                ,{"Product item", Text.Type}
            }
in
    Source

let
    Source = Excel.CurrentWorkbook(){[Name="sys_export"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,col_to_select),
    Custom1 = Table.SelectColumns(#"Changed Type", List.Transform(col_to_select, each List.First(_)))
in
    Custom1

You can extend the inner lists, so you can have the original name of import, the source or supplier, the to be name and the type.

 

[Paul0702]

You have Table.SelectColumns() function in Power Query.
You could have a query in PBI that contains a list with the columns needed. And in the Table.SelectColumns() step of the result query, you can refer to this list.
Assunign this data "sys_export"

Some date	Value	sys posting date	Client	Invoice Number	Tax regime	Product item
9/01/2025	500	25/01/2025	ACME	20250126-00001	A	ITM-CRD-5478
10/01/2025	742	25/01/2025	MYC°	20250126-00002	A	ITM-BKE-3651
11/01/2025	349	25/01/2025	ACE	20250126-00003	B	ITM-KNF-9999

Query with list of column names: col_to_select

[RANGE=rs:4|cs:7|v:table|w:Book1|cls:xl2bb-210|s:Sheet1|tw:492][XR][XD=ch:0|w:67|cls:h][/XD][XD=ch:0|w:48|cls:h][/XD][XD=ch:0|w:93|cls:h][/XD][XD=ch:0|w:48|cls:h][/XD][XD=ch:0|w:92|cls:h][/XD][XD=ch:0|w:68|cls:h][/XD][XD=ch:0|w:77|cls:h][/XD][/XR][XR][XD=h:l|fw:b|bc:156082|c:FFFFFF|ch:15|cls:bl bt bb]Some date[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Value[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]sys posting date[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Client[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Invoice Number[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt bb]Tax regime[/XD][XD=h:l|fw:b|bc:156082|c:FFFFFF|cls:bt br bb]Product item[/XD][/XR][XR][XD=bc:C0E6F5|ch:15|cls:bl bt bb|tx:45666|nf:m/d/yyyy]9/01/2025[/XD][XD=bc:C0E6F5|cls:bt bb]500[/XD][XD=bc:C0E6F5|cls:bt bb|tx:45682|nf:m/d/yyyy]25/01/2025[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]ACME[/XD][XD=bc:C0E6F5|cls:bt bb]20250126-00001[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]A[/XD][XD=h:l|bc:C0E6F5|cls:bt br bb]ITM-CRD-5478[/XD][/XR][XR][XD=ch:15|cls:bl bt bb|tx:45667|nf:m/d/yyyy]10/01/2025[/XD][XD=cls:bt bb]742[/XD][XD=cls:bt bb|tx:45682|nf:m/d/yyyy]25/01/2025[/XD][XD=h:l|cls:bt bb]MYC°[/XD][XD=cls:bt bb]20250126-00002[/XD][XD=h:l|cls:bt bb]A[/XD][XD=h:l|cls:bt br bb]ITM-BKE-3651[/XD][/XR][XR][XD=bc:C0E6F5|ch:15|cls:bl bt bb|tx:45668|nf:m/d/yyyy]11/01/2025[/XD][XD=bc:C0E6F5|cls:bt bb]349[/XD][XD=bc:C0E6F5|cls:bt bb|tx:45682|nf:m/d/yyyy]25/01/2025[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]ACE[/XD][XD=bc:C0E6F5|cls:bt bb]20250126-00003[/XD][XD=h:l|bc:C0E6F5|cls:bt bb]B[/XD][XD=h:l|bc:C0E6F5|cls:bt br bb]ITM-KNF-9999[/XD][/XR][/RANGE]

Queyr with applied selection referring to that list.

let
    Source = Excel.CurrentWorkbook(){[Name="sys_export"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Some date", type datetime}, {"Value", Int64.Type}, {"sys posting date", type datetime}, {"Client", type text}, {"Invoice Number", type text}, {"Tax regime", type text}, {"Product item", type text}}),
    Custom1 = Table.SelectColumns(#"Changed Type", col_to_select)
in
    Custom1

If with formatting , you mean data types, the you can create a list of lists. Where the inner list contains 2 items, the column name and the data type.

//cols_to_select
let
    Source = {
                 {"Some date", Date.Type}
                ,{"Value", Number.Type}
                //,"sys posting date"
                ,{"Client", Text.Type}
                //,"Invoice Number"
                ,{"Tax regime", Text.Type}
                ,{"Product item", Text.Type}
            }
in
    Source

let
    Source = Excel.CurrentWorkbook(){[Name="sys_export"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,col_to_select),
    Custom1 = Table.SelectColumns(#"Changed Type", List.Transform(col_to_select, each List.First(_)))
in
    Custom1

You can extend the inner lists, so you can have the original name of import, the source or supplier, the to be name and the type.

Thank you for the information - if I were to have a separate spreadsheet with a column of the transaction comments or apart of them so BI table can look up the part and change it to be cleaner how will i go about doing that from what you have given. like do i keep both the raw data sheet and the clean up sheet in the same folder for power bi to look at to get the information that i want the final product to be

Thank you again

 

[Paul0702]

Basically for the custom i would want something like:

= if the [GL comment] contains some from the cleanup sheet (sheet 1) column 1 then return result from column 2

so of the GL comments have the same wording or data or has a date so I would want something like if the [GL comment} has anyting containing said comment to look for it on the cleanup sheet

Like if the comment is "transaction summary 1/30/25" i would want the formula to look for transaction each time to change it to lets say MISC

thank you

 

[GraH]

Thank you for the information - if I were to have a separate spreadsheet with a column of the transaction comments or apart of them so BI table can look up the part and change it to be cleaner how will i go about doing that from what you have given. like do i keep both the raw data sheet and the clean up sheet in the same folder for power bi to look at to get the information that i want the final product to be

Thank you again

It does matter where the source is. It depends on what's convenient or not. I prefer to have the "raw" data in a folder "raw" and any reference, like your clean-up sheet, in a folder "references".

Basically for the custom i would want something like:

= if the [GL comment] contains some from the cleanup sheet (sheet 1) column 1 then return result from column 2

so of the GL comments have the same wording or data or has a date so I would want something like if the [GL comment} has anyting containing said comment to look for it on the cleanup sheet

Like if the comment is "transaction summary 1/30/25" i would want the formula to look for transaction each time to change it to lets say MISC

thank you

Many road could lead to Rome, again it depends on what's most efficient or sustainable or enables easy code writing.

One could split the "comment field" into a list of words with Text.Split( [Comment Field], " "). Whether or not to remove all punctuation is an option. List.ContainsAny can allow you to test if this field contains a list of keywords from your clean-up sheet. As it will return true or false, it's easily wrapped inside an if-then-else statement.
But I understand "misc" needs to become the header of the column. How does that work if you'd have different records?

Alternatively, one could go with the option to keep a list of certain columns and rename comments to "Misc".

Sample (fake/obfuscated) data could be useful here, as the patterns can be both easy or very complex to deal with.

 

[Paul0702]

It does matter where the source is. It depends on what's convenient or not. I prefer to have the "raw" data in a folder "raw" and any reference, like your clean-up sheet, in a folder "references".


Many road could lead to Rome, again it depends on what's most efficient or sustainable or enables easy code writing.

One could split the "comment field" into a list of words with Text.Split( [Comment Field], " "). Whether or not to remove all punctuation is an option. List.ContainsAny can allow you to test if this field contains a list of keywords from your clean-up sheet. As it will return true or false, it's easily wrapped inside an if-then-else statement.
But I understand "misc" needs to become the header of the column. How does that work if you'd have different records?

Alternatively, one could go with the option to keep a list of certain columns and rename comments to "Misc".

Sample (fake/obfuscated) data could be useful here, as the patterns can be both easy or very complex to deal with.

Thank you again for your help but i am still new to all the formulas and how they work in power BI so sorry if I need a simpler explanation.

So i have the cleanup sheet and "raw" data in the same folder. and i "get data" the clean up sheet. How/which formula (easiest) do I use so that the GL comment (whatever it may be -ex. Vendor Name) from the raw data will look at the cleanup sheet (column1 - ex which has the generic vendor name) and get the results (column 2 of cleanup sheet - ex how I want the vendor name to appear).

I tried ListContainsAny but I could not figure out how to get the correct wording or formula layout to get the results I want

 

[GraH]

Like I said earlier, sample data is required. Best way to do is the via the XL2BB add-on.

 

[Paul0702]

Unfortunately, I am unable to share the exact data but I can include screenshots to show what I am trying to do. The first image is the data in the Power BI table already - the last column is what i am trying to clean up so that is is only the vendor name like BC & B Graphics. The second image in the other table that I have in Power BI with the Get Data function.

There is where i am stuck, how do i get the formula to work between the two tables so that i can get a cleaner view of only the vendor names. Please let me know if you need more information or better screen shots. The third image is the tables included in Power BI to pull the data together to get the end result i want.

thank you so much for all your help so far.

 

[Paul0702]

A better example for the vendor names would be like someone posts "Diebold" and i would need it to say "Diebold Inc. " - there would be invoices posted with the correct naming convention but some manual posts will have "Diebold /12345" so the clean up table would have "Diebold" in the first column and "Diebold Inc. " in the second column

So I would need a formula to look at the GL_DetailCondensed[GL Comment] and then search in Expense[GL Comment] find the vendor name (partial match) and return Expense[Expenses] column 2

thank you again