Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
When attempting to do modifications through PowerQuery on a raw dataset, any modifications change the table values. The values seem to shift over by one column. I cannot figure out why it is doing that. The values should be static with the rows and columns with which they are associated. It's almost like the value get shifted over to a different column.
1. I have pulled data from QuickBooks desktop using an ODBC. The raw data that is pulled in is accurate (see screen shot).
2. Any attempt at any modification of the data (filter rows, demote headers, etc.) changes the row values. I cannot understand why the row values are changing and what they are being changed to.
a. I filter column "Label_315" to just show "4000 - Consulting Income". Notice how the columns change row values when it is filtered up.
b. I take the same raw data and do a "demote header" function. The same thing happens. The column/row associated values change.
Here is the entire M for when I am filtering:
let
Source = Odbc.DataSource("dsn=CData QuickBooks Sys (64)", [HierarchicalNavigation=true]),
CData_Database = Source{[Name="CData",Kind="Database"]}[Data],
QuickBooks_Schema = CData_Database{[Name="QuickBooks",Kind="Schema"]}[Data],
PL_JOB_v1_View = QuickBooks_Schema{[Name="PL_JOB_v1",Kind="View"]}[Data],
#"Filtered Rows" = Table.SelectRows(PL_JOB_v1_View, each ([Label_315] = "4000 · Consulting Income"))
in
#"Filtered Rows"
It's possible you have some of the steps cached. If you click Refresh Preview on the various steps, does anything change?
No. I've also restarted the connection and repulled the data from scratch as well and the same things happen.
OK. I don't know what's happening then.
Maybe sharing the M code for the query would help?
The DAX code is in the screenshots.
FYI, that's not DAX, it's M. Some of it is in the screenshots but copying and pasting the full code that shows up in the Advanced Editor is what I meant.
Got it. Thanks for the clarification.
Here is the M:
let
Source = Odbc.DataSource("dsn=CData QuickBooks Sys (64)", [HierarchicalNavigation=true]),
CData_Database = Source{[Name="CData",Kind="Database"]}[Data],
QuickBooks_Schema = CData_Database{[Name="QuickBooks",Kind="Schema"]}[Data],
PL_JOB_v1_View = QuickBooks_Schema{[Name="PL_JOB_v1",Kind="View"]}[Data],
#"Filtered Rows" = Table.SelectRows(PL_JOB_v1_View, each ([Label_315] = "4000 · Consulting Income"))
in
#"Filtered Rows"
Thanks for the update. I'm not seeing anything suspicious looking here though so I don't have any new hypotheses about what's happening. Sorry.
Crazy. Thanks for trying.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.