Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
This is my query... (the problem is I need to combine CUSTOMERACCOUNT numbers beginning in 90, 900 or 9 with it's matching account 880, 34 or 1178 but dropping the 90, 900 or 9. I tried using replace text but that didn't work.
let
Source = Sql.Database("hadbax1", "ProductionAX2012"),
dbo_HOS_SALEHST = Source{[Schema="dbo",Item="HOS_SALEHST"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_HOS_SALEHST, each [INVOICEDATE] >= #datetime(2020, 1, 1, 0, 0, 0) and [INVOICEDATE] <= #datetime(2021, 12, 31, 0, 0, 0)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([ITEMCLASS] <> "2100" and [ITEMCLASS] <> "2101" and [ITEMCLASS] <> "2102" and [ITEMCLASS] <> "2103" and [ITEMCLASS] <> "2104" and [ITEMCLASS] <> "2106" and [ITEMCLASS] <> "2150" and [ITEMCLASS] <> "3920" and [ITEMCLASS] <> "9900" and [ITEMCLASS] <> "9999")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [INVOICEDATE] >= #datetime(2021, 1, 1, 0, 0, 0) and [INVOICEDATE] <= #datetime(2021, 12, 31, 0, 0, 0)),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each ([ITEMCLASS] <> "")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each [INVOICEDATE] >= #datetime(2021, 1, 1, 0, 0, 0) and [INVOICEDATE] <= #datetime(2021, 12, 31, 0, 0, 0)),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each ([CUSTOMERCLASS] = "DS") and ([COMPANYNUM] = "01"))
in
#"Filtered Rows5"
Make sure that the CUSTOMERACCOUNT column is type text, then try typing this into the formula bar:
Table.TransformColumns(PriorStepName, {{"CUSTOMERACCOUNT", each Text.Range(_, 0, 2)}})
--Nate
Thank you for trying. But when I go to that column, it is filtered and shows
= Table.SelectRows(#"Filtered Rows4", each ([CUSTOMERCLASS] = "DS") and ([COMPANYNUM] = "01"))
I did try adding it but nothing worked. Thanks.
Tracy
Hi @tmcateer4
Do share some sample input data and the expected output for it. It will help clarify what you need.
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
This is an example of some of the data. The CUSTOMERACCOUNT includes a possible two or more account numbers such as 90034 or 34 and 90880 and 880. I want to drop the first three to 34 or first two numbers to 880 respectively whereas I can sum the net sales with the account numbers to one account number. How would I do that? Thanks so much!