Eliminate or select specific columns of a source PowerPivot table in an OLE DB Query

[RKN]

Eliminate or select specific columns of a source PowerPivot table in an OLE DB Query

To get around some of the formatting limitations of a pivot table, I managed to build an OLE DB Query table which pulls data from a source PowerPivot table into excel. The following connection specs currently queries all of the fields from the source PowerPivot table. How do I modify the connection command text to eliminate specific columns or to select only certain columns from the Powerpivot table source? I know that one option is to simply delete the unwanted columns in the resulting query table, but I would like to avoid that approach since the deleted columns will be automatically added back if I edit the query in the future. Please be specific as I am a novice with PowerPivot and DAX and I am not familar with the syntax. My IT department says I just know enough to be dangerous. Thanks in advance! Rob

Connection:
Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

Command Type:
Default

Command Text:
Evaluate Table_ReconcilingInstruments

 

[JavierGuillen]

Try using a combination of SUMMARIZE and ADDCOLUMNS.

evaluate

addcolumns(
summarize(table_ReconcilingInstruments, table_ReconcilingInstruments_KeyColumn<TABLE_RECONCILINGINSTRUMENTS-KEYCOLUMN>),
"col1", table_ReconcilingInstruments[col1],
"col2", table_ReconcilingInstruments[col2]
)

 

[RKN]

Thank you!

 

[RKN]

I have the following query going...
Evaluate
AddColumns(Summarize("Table_ReconcilingInstruments","Table_ReconcilingInstruments[Instrument Number]"),"Source Split Grade Flag", "Table_ReconcilingInstruments[Source Split Grade Flag]","Primary Name", "Table_ReconcilingInstruments[Primary Name]","Class Code Desc", "Table_ReconcilingInstruments[Class Code Desc]"...

However, it errors out and indicates that I need a scalar expression for the Table_ReconcilingInstruments[Instrument Number] column added by the summarize function. Ive made several attempts but not having any luck with the syntax for it. The Instrument number value needs to be greater than 0 or not blank. Any guidance with the proper syntax for the scalar expression would be much appreciated.

 

[JavierGuillen]

Your parameters should not go with double quotes unless they are a new column name.

Evaluate
AddColumns(Summarize(Table_ReconcilingInstruments,Table_ReconcilingInstruments[Instrument Number]),"Source Split Grade Flag", Table_ReconcilingInstruments[Source Split Grade Flag],"Primary Name", Table_ReconcilingInstruments[Primary Name],"Class Code Desc", Table_ReconcilingInstruments[Class Code Desc]...

 

[RKN]

Thank you for your help. I was troubleshooting another error and forgot to delete the quotes. For ease of troubleshooting the following is the entire command text for the query:
Evaluate
AddColumns(Summarize(Table_ReconcilingInstruments,Table_ReconcilingInstruments[Instrument Number]),"Source", Table_ReconcilingInstruments[Source],"Instrument Number", Table_ReconcilingInstruments[Instrument Number],"Accrued Interest", Table_ReconcilingInstruments[Accrued Interest])

However, I recieve the following error message: "The value for column 'Accrued Interest' in table 'Table_ReconcilingInstruments' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies."

I double checked and all column references in the above statement check out to the source powerpivot table and there are no errors or circular dependencies that I can see in the source data. Any ideas what the problem is?

 

[JavierGuillen]

I did forget to mention one thing:

In order to propagate context through ADDCOLUMNS, you must use the CALCULATE function. Try:

Evaluate

AddColumns(

Summarize(

Table_ReconcilingInstruments,

Table_ReconcilingInstruments[Instrument Number]

),

"Source", Calculate(values(Table_ReconcilingInstruments[Source])),

"Instrument Number", Calculate(values(Table_ReconcilingInstruments[Instrument Number])),

"Accrued Interest", Calculate(values(Table_ReconcilingInstruments[Accrued Interest]))

)