Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Does anyone know if/how this can be done?
I have a table in the Query Editor that has a number of fields, one of which is the Customer #. I want to make a new column in this table that contains Region, by looking up the Customer # in another table that contains Customer Master data. Is there an easy way to do this? I was thinking if I could do this, it would perform faster than if I used DAX after the tables were brought into the file. Here's an example of the query for the orders file for which I'd like to use the customer # to lookup the Region in that other table.
let
Source = Sql.Databases("csmdataservice.cloudapp.net"),
CSM_Master = Source{[Name="CSM_Master"]}[Data],
dbo_OrdersView = CSM_Master{[Schema="dbo",Item="OrdersView"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_OrdersView,{{"Line_Creation_Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Line_Creation_Date] >= #date(2016, 10, 1)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [Sold_To_APR] & "|" & [Sold_To_Party_Description]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "%APRBPIDKey"}})
in
#"Renamed Columns"
Any help is appreciated. Seems @ImkeF is really good with M. : )
Solved! Go to Solution.
Hi @Shelley,
Please check out the demo in the attachment. It's a solution in Power Query (the Custom column).
(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]
Best Regards,
Dale
Hi @Shelley,
Please check out the demo in the attachment. It's a solution in Power Query (the Custom column).
(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]
Best Regards,
Dale
Great post.
Thank you dear for this useful demo , I have very big data, I tried it but when I implemint it the custom column take long time and not responed at all , do you think this method not useful for big number of rows? are there another solution other than dax?
Did you ever find a solution to this being so slow? I also have big data and whether I do a merge as new or use the suggested solution, it takes forever....
thank you so much, you saved my life!
Thanks for post, it helped a lot.
Thank you so much! This has helped me greatly. Can you please explain the logic in this power query custom column ( such as what does the "{0}" do in the query?). Also, how does this compare to using Power Query Merge?
I am wondering the same thing.
Specifically, since we can either use the custom lookup or the merge to achieve the desired result...would like to know which one is better in terms of efficiency and performance.
@MFelix @v-jiascu-msft @Chihiro
Can anyone provide some insight? Thank you for your help!
Hi, I tried this but it made my already big table (1giga) became >1 tera. And paradoxally i did the step to lookup a column in another table to make the initial 1giga table smaller by filtering on the custom column.
Is there way to reduce the table as I want before closing the query but not making the upload of the query so slow?
I tried to apply the formula to create a custom "lookup" column but it fails.
(let QId = [Q-C] in Table.SelectRows(Metadata, each [Q-C] = QId)){0}[Question]
Error: Invalid identifier and red line appear below "in" in above formula.
I have "Metadata" table that contains columns Q-C, Questions.
In another table "QwC" I have column Q-C and where ever value of "QwC-QC" matches "Metadata-Q-C", populate the custom column "Question" in "QwC" with value of "Metadata-Question"
Thank you in advance for any help in this regard.
@v-jiascu-msft Thanks for your help in the past. I am now trying to lookup a profit center description with a profit center code and am getting an error.
Here's the M:
let
Source = Sql.Databases("csmdataservice.cloudapp.net"),
CSM_Master = Source{[Name="CSM_Master"]}[Data],
dbo_ProfitCenterGrouping = CSM_Master{[Schema="dbo",Item="ProfitCenterGrouping"]}[Data],
#"Added Custom" = Table.AddColumn(dbo_ProfitCenterGrouping, "%Section|ProductLineKey", each [Section] & "|" & [Product_Line_FIN]),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Core?", each if [Flag_Core] = true then "Core" else "NonCore"),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"PC", "Product_Line_FIN", "Section", "%Section|ProductLineKey", "Core?"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Product_Line_FIN"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Product_Line_FIN", "Product Line"}, {"Section", "Product Group"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each (let currentProfitCenter = [PC] in Table.SelectRows(OrdersView_Profit_Center_Description_Lookup, each [Profit_Center] = currentProfitCenter)){0}[Profit_Center_Description])
in
#"Added Custom2"
I'm trying to look up the profit center code [PC] from this table into the Ordersview_Profit_Center_Description_Lookup reference table (field = Profit_Center) to get the Profit_Center_Description value, but am receiving this error:
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
Table
What the heck am I doing wrong? Thanks for your help!
I am too getting same error. Any solution?
(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]
Intead of CustomerID if i want a static text say "Cust" to be entered in the custom column then what is the syntax to achieve this.
Also if the customer name present in table 2 is not present in table 1 I want to add another text say "NA".
For example if there is a customer G in table 2 and no entry for customer G in table 1 then I want to enter value as "NA" in custom column.
Thanks & Regards,
Sonam
@v-jiascu-msftThanks for the help, Dale! I got it to work!
I was experiencing a couple errors due to bad data.
1. I discovered a data type mismatch - i.e. the data type on the distributor ID in one table was different than the other table - so I was receiving an error. I changed the data types to match and then this part worked.
2. Some of the distributor IDs were null or not in the master table and so some of the cells in the column again gave the same error. I didn't need these records, so I removed the errors.
I don't remember the exact wording of the error - something about enumerations. (I had this all typed out and then Microsoft took the site down for maintenance and everything I wrote was lost.)
THANKS AGAIN!!
You could use standard Merge Query UI tool.
Or in "M" something like...
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"KeyColumnName"},LookupTableName,{"RelatedColumnName"},"NewColumnName",JoinKind.LeftOuter)
Then expand Region.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe reason I want to do this is because I need to make a Key column that is Customer#|Region. I am trying to tie a whole bunch of fact tables together with a Link Table, using the key I create from customer# + region. If I don't create the key in the Query Editor, I was thinking the DAX will be slower.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, the same customer# can be responsible for more than one region. I also have a number of diverse fact tables. So I need the unique key to tie all the fact tables together correctly.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
84 | |
76 | |
49 |
User | Count |
---|---|
143 | |
141 | |
109 | |
69 | |
55 |