Power Query: Import Values only From Table

[takoyaki]

Hi all,

Is there any way to import values only from a table, rather than formulas? My current code is:

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

How would I rewrite this to bring in all calculated formulas as hard-coded values? The issue is this:

I have two tables I'm importing; one is the original table and all of its values should be hard-coded. The second table is a copy of the first where users make changes to the values; it includes formulas that update when changes are made by the user. The M-Code subtracts the values from these two tables from each other to determine the deltas between them. The issue is that Power Query is reading the second table's formulas as different from the first table's hard-coded values, even when they evaluate to the same result. Is there any way the second table's content could be read as the evaluated values rather than as its formulas?

Here is the full M-Code:

let
    Original = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Copy = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Source = Original&Copy,
    ReplacedValue = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,List.Skip(Table.ColumnNames(Source),50)),
    DateHeaders = List.Transform(List.Skip(Table.ColumnNames(ReplacedValue),50),each {_,(x)=>let first=Table.Column(x,_) in first{0}-first{1}}),
    NonDateHeaders = List.FirstN(Table.ColumnNames(ReplacedValue),50),
    Result = Table.Group(ReplacedValue,NonDateHeaders,DateHeaders),
in
    Result

 

[RoryA]

PQ only reads the values of the cells, so whatever the issue is, it is not that it's reading the formulas. It may however be rounding errors or issues with floating point arithmetic. Hard to say without a concrete example.

 

[takoyaki]

PQ only reads the values of the cells, so whatever the issue is, it is not that it's reading the formulas. It may however be rounding errors or issues with floating point arithmetic. Hard to say without a concrete example.

Hi Rory, thanks for the response! Upon further investigation, I've determined what I believe the real issue is. In the Copy table, when changes are being made, formulas are updating and resulting in different values in those NonDateHeader columns. I believe PQ is having a hard time matching these columns in the Original and Copy afterward and is unable to subtract the values from one table from another because of this. If there was some way to compute the subtractions without appending the tables to one another I believe this would solve the issue but I'm not sure how.

 

[RoryA]

If you're trying to compare them, why are you appending rather than merging?

 

[takoyaki]

If you're trying to compare them, why are you appending rather than merging?

I am admittedly new to Power Query, if there is a better way to solve the problem I'm all ears! Would merging be an easier way to subtract the DateHeaders column values from the two tables while ignoring all the values in the NonDateHeaders, any of which are subject to change?

 

[RoryA]

I have no idea what your datasets look like, so it's pretty hard to say!

 

[takoyaki]

Sorry about that, here is a picture:

Delta would be my desired result.

 

[RoryA]

So how would PQ know which row to compare to which? If it's just in data order, then you could add an index column to each, link on that in the merge, then bring in all the date columns from the merged query and add calculated columns for each subtraction.

 

[takoyaki]

So how would PQ know which row to compare to which? If it's just in data order, then you could add an index column to each, link on that in the merge, then bring in all the date columns from the merged query and add calculated columns for each subtraction.

So you are saying I'd need 5 calculated columns (in this scenario) to subtract the values in each date column? What if I have a different amount of date columns with different header names? Is there a way this calculation can be done with one transformation, similar to how I wrote it initially?

 

[RoryA]

You can do something like this:

let
    Original = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 0, 1), 
    Copy = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table2"]}[Content], "Index", 0, 1), 
    Destination = Table.SelectColumns(Original,{"Column1", "Column2", "Column3", "Column4", "Column5", "Index"}),
    UnpivotedOriginal = Table.UnpivotOtherColumns(Table.RemoveColumns(Original, {"Column1", "Column2", "Column3", "Column4", "Column5"}), {"Index"}, "Attribute", "Value"),
    UnpivotedCopy = Table.UnpivotOtherColumns(Table.RemoveColumns(Copy, {"Column1", "Column2", "Column3", "Column4", "Column5"}), {"Index"}, "Attribute", "Value"),
    Source = Table.NestedJoin(UnpivotedOriginal,{"Index", "Attribute"},UnpivotedCopy,{"Index", "Attribute"},"Copy",JoinKind.Inner),
    ExpandCopy = Table.ExpandTableColumn(Source, "Copy", {"Value"}, {"Copy.Value"}),
    AddDifference = Table.AddColumn(ExpandCopy, "Difference", each [Copy.Value]-[Value]),
    RemovedColumns = Table.RemoveColumns(AddDifference,{"Value", "Copy.Value"}),
    RepivotColumns = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Attribute]), "Attribute", "Difference", List.Sum),
    MergeQueries = Table.NestedJoin(RepivotColumns,{"Index"},Destination,{"Index"},"Destination",JoinKind.Inner),
    ExpandFinal = Table.ExpandTableColumn(MergeQueries, "Destination", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    FinalRemoveColumns = Table.RemoveColumns(ExpandFinal,{"Index"})
in
    FinalRemoveColumns

I'll leave you to incorporate your line to get the non date headers...