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

 

[takoyaki]

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...

Thank you thank you thank you!!! Here is my complete M-Code, accounting for those NonDateHeaders (of which there are 50 total).

let
    Original = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NonDateHeaders = List.FirstN(Table.ColumnNames(Original),50),
    DateHeaders = List.Skip(Table.ColumnNames(Original),50),
    ReplaceNulls = Table.ReplaceValue(Original,null,0,Replacer.ReplaceValue,DateHeaders),
    Original1 = Table.AddIndexColumn(ReplaceNulls, "Index", 0, 1),
    AllColumnHeaders = Table.ColumnNames(Original1),
    ReorderedList = List.Combine({{"Index"},List.FirstN(AllColumnHeaders,List.Count(AllColumnHeaders)-1)}),
    Original2 = Table.ReorderColumns(Original1,ReorderedList),
    NonDateHeadersI = List.FirstN(Table.ColumnNames(Original2),51),

    Copy = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ReplaceNulls2 = Table.ReplaceValue(Copy,null,0,Replacer.ReplaceValue,DateHeaders),
    Copy1 = Table.AddIndexColumn(ReplaceNulls2, "Index", 0, 1),
    Copy2 = Table.ReorderColumns(Copy1,ReorderedList),

    Destination =  Table.SelectColumns(Original2, NonDateHeadersI),
    UnpivotedOriginal = Table.UnpivotOtherColumns(Table.RemoveColumns(Original2, NonDateHeaders), {"Index"}, "Attribute", "Value"),
    UnpivotedCopy = Table.UnpivotOtherColumns(Table.RemoveColumns(Copy2, NonDateHeaders), {"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", NonDateHeaders, NonDateHeaders),
    FinalRemoveColumns = Table.RemoveColumns(ExpandFinal, {"Index"}),
    ReorderedList2 = List.Combine({NonDateHeaders, DateHeaders}),
    FinalReorderColumns = Table.ReorderColumns(FinalRemoveColumns,ReorderedList2)

in
    FinalReorderColumns

Several things to note:
1) If you have any columns that are entirely null, you must replace these values with 0 or they will be filtered out for some reason.
2) For some reason, most likely my own doing, the NonDateHeaders were ordered after the DateHeaders following FinalRemoveColumns, necessitating a reorder.

Thank you again, Rory. I learned a lot from your help

 

[JGordon11]

Here's a different approach

let
    tbl1 = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index"),
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn = List.Select(Table.ColumnNames(tbl1), each try Value.Is(Date.From(_), type date) otherwise false),
    tbl = Table.FromRecords(Table.TransformRows(tbl1, each 
        let TransformOperations = List.Accumulate(tcn, {}, (s,c)=> s & {{c, (x)=> Table.Column(tbl2, c){[Index]} - x }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(tbl, {"Index"})
in
    Result

 

[takoyaki]

Here's a different approach

let
    tbl1 = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index"),
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn = List.Select(Table.ColumnNames(tbl1), each try Value.Is(Date.From(_), type date) otherwise false),
    tbl = Table.FromRecords(Table.TransformRows(tbl1, each
        let TransformOperations = List.Accumulate(tcn, {}, (s,c)=> s & {{c, (x)=> Table.Column(tbl2, c){[Index]} - x }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(tbl, {"Index"})
in
    Result

This is a work of art, so concise, so beautiful. I especially like this because I won't encounter an error if there are no date columns (these tables are blank templates that I populate with a macro). Thank you both for all of your help. One thing to note is that I had to replace all nulls with 0. Here is my updated code, future Googler:

let
    Original = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index"),
    Original0 = Table.ReplaceValue(Original,null,0,Replacer.ReplaceValue,List.Skip(Table.ColumnNames(Original),50)),
    Copy = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Copy0 = Table.ReplaceValue(Copy,null,0,Replacer.ReplaceValue,List.Skip(Table.ColumnNames(Copy),50)),
    DateHeaders = List.Select(Table.ColumnNames(Original0), each try Value.Is(Date.From(_), type date) otherwise false),
    Difference = Table.FromRecords(Table.TransformRows(Original0, each
        let TransformOperations = List.Accumulate(DateHeaders, {}, (s,c)=> s & {{c, (x)=> Table.Column(Copy0, c){[Index]} - x }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(Difference, {"Index"}),

in
    Result

 

[JGordon11]

Here's another way to handle the nulls so you don't have to hard-code 50 columns to skip

let
    Original = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index"),
    Copy = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    DateHeaders = List.Select(Table.ColumnNames(Original), each try Value.Is(Date.From(_), type date) otherwise false),
    N = (a as nullable number) as number => if a <> null then a else 0,
    Difference = Table.FromRecords(Table.TransformRows(Original, each
        let TransformOperations = List.Accumulate(DateHeaders, {}, (s,c)=> s & {{c, (x)=> N(Table.Column(Copy, c){[Index]}) - N(x) }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(Difference, {"Index"})
in
    Result

 

[takoyaki]

Here's another way to handle the nulls so you don't have to hard-code 50 columns to skip

let
    Original = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index"),
    Copy = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    DateHeaders = List.Select(Table.ColumnNames(Original), each try Value.Is(Date.From(_), type date) otherwise false),
    N = (a as nullable number) as number => if a <> null then a else 0,
    Difference = Table.FromRecords(Table.TransformRows(Original, each
        let TransformOperations = List.Accumulate(DateHeaders, {}, (s,c)=> s & {{c, (x)=> N(Table.Column(Copy, c){[Index]}) - N(x) }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(Difference, {"Index"})
in
    Result

Thank you so much, it works perfectly!!!

 

[takoyaki]

Here's another way to handle the nulls so you don't have to hard-code 50 columns to skip

let
    Original = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index"),
    Copy = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    DateHeaders = List.Select(Table.ColumnNames(Original), each try Value.Is(Date.From(_), type date) otherwise false),
    N = (a as nullable number) as number => if a <> null then a else 0,
    Difference = Table.FromRecords(Table.TransformRows(Original, each
        let TransformOperations = List.Accumulate(DateHeaders, {}, (s,c)=> s & {{c, (x)=> N(Table.Column(Copy, c){[Index]}) - N(x) }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(Difference, {"Index"})
in
    Result

Unfortunately, this code worked great for my data set of about 40 rows but experienced significant performance delays with a dataset of about 300 rows. Reverting to RoryA's approach resolved all performance issues. I'm going to try incorporating some of JGordan11's ideas into my longer code to clean it up a bit.

 

[JGordon11]

See if this speeds it up any

let
    Original = Table.Buffer(Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index")),
    Copy = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]),
    DateHeaders = List.Buffer(List.Select(Table.ColumnNames(Original), each try Value.Is(Date.From(_), type date) otherwise false)),
    N = (a as nullable number) as number => if a <> null then a else 0,
    Difference = Table.FromRecords(Table.TransformRows(Original, each
        let TransformOperations = List.Accumulate(DateHeaders, {}, (s,c)=> s & {{c, (x)=> N(Table.Column(Copy, c){[Index]}) - N(x) }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(Difference, {"Index"})
in
    Result

 

[takoyaki]

See if this speeds it up any

let
    Original = Table.Buffer(Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Index")),
    Copy = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]),
    DateHeaders = List.Buffer(List.Select(Table.ColumnNames(Original), each try Value.Is(Date.From(_), type date) otherwise false)),
    N = (a as nullable number) as number => if a <> null then a else 0,
    Difference = Table.FromRecords(Table.TransformRows(Original, each
        let TransformOperations = List.Accumulate(DateHeaders, {}, (s,c)=> s & {{c, (x)=> N(Table.Column(Copy, c){[Index]}) - N(x) }})
        in  Record.TransformFields(_, TransformOperations) )),
    Result = Table.RemoveColumns(Difference, {"Index"})
in
    Result

This helped significantly! Thank you!!!