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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Power BI - importing conditional clauses
- Thread starter Paul0702
- Start date
GraH
Well-known Member
- Joined
- Mar 22, 2020
- Messages
- 1,583
- Office Version
- 365
- Platform
- Windows
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"
Query with list of column names: col_to_select
Queyr with applied selection referring to that list.
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.
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.
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
Power Query:
[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.
Power Query:
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
Custom1If 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.
Power Query:
//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
Power Query:
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
Custom1You 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.
Upvote
0
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
Upvote
0
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
= 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
Upvote
0
Similar threads
- Question
