locked
Power Query to Arrange data and lookup values RRS feed

  • Question

  • Hi All,

    I have 1 sheet where "A5.2-ab=2 [N;N] | U1-a=1 [N;N] | U2=2 [N;N]" types of values mentioned and I need to lookup the values from reference sheet and need to show the total sum.

    For example : A5.2-ab=2 [N;N] | U1-a=1 [N;N] | U2=2 [N;N]

    In Power query 1 its search for A5.2-ab value in reference sheet in current case its 810*2+then search for U1-a in reference sheet which is 1890*1+then search for U2=1930*2.

    unit price count total
    810 2 812
    1890 1 1891
    1930 2 1932

    4635

    Total should be 4630.

    I am uploading the sample file in below given link.

    https://onedrive.live.com/redir?resid=393CD98CEE2A349F!145&authkey=!AH_NqHe9f5aUks0&ithint=file%2cxlsx

    Thanks in advance for the valuable suggestion.

    Regards,

    Rajender

    Tuesday, March 15, 2016 4:06 PM

Answers

  • Hi Rajender,

    you can try this code, that builds on Bertrands very smart solution (nice trick when splitting "]" :-)):

    let
        Source = Excel.CurrentWorkbook(){[Name="Summary"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"CR_Nr", Int64.Type}, {"List of 'SM_ICTI' ", type text}, {"Total Price", type any}}),
        Index = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Filtered Rows" = Table.SelectRows(Index, each ([Total Price] <> "No SM or ICTI !!!")),
        SplitCriteria = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Split([#"List of 'SM_ICTI' "], "|")),
        ExpandSingleCriteria = Table.ExpandListColumn(SplitCriteria, "Custom"),
        ExtractRelevantPart = Table.SplitColumn(ExpandSingleCriteria,"Custom",Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv),{"Custom.1"}),
        SplitLookupFromMultiply = Table.SplitColumn(ExtractRelevantPart,"Custom.1",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Custom.1.1", "Custom.1.2"}),
        ChangeType = Table.TransformColumnTypes(SplitLookupFromMultiply,{{"Custom.1.1", type text}, {"Custom.1.2", Int64.Type}}),
        TrimCleanLookupString = Table.TransformColumns(ChangeType,{{"Custom.1.1", Text.Trim}}),
        LowercaseLookupString = Table.TransformColumns(TrimCleanLookupString,{{"Custom.1.1", Text.Lower}}),
        MergeLookupTable = Table.NestedJoin(LowercaseLookupString,{"Custom.1.1"},Reference,{"Unit"},"NewColumn",JoinKind.LeftOuter),
        ShowValues = Table.ExpandTableColumn(MergeLookupTable, "NewColumn", {"Price"}, {"Price"}),
        Multiply = Table.AddColumn(ShowValues, "Multiply", each List.Product({[Custom.1.2], [Price]}), Int64.Type),
        GroupByItem = Table.Group(Multiply, {"Index"}, {{"Result", each List.Sum([Multiply]), type number}}),
        MergeFilteredRowsBack = Table.NestedJoin(Index,{"Index"},GroupByItem,{"Index"},"NewColumn",JoinKind.LeftOuter),
        ShowResult = Table.ExpandTableColumn(MergeFilteredRowsBack, "NewColumn", {"Result"}, {"Result"}),
        #"Sorted Rows" = Table.Sort(ShowResult,{{"Index", Order.Ascending}})
    in
        #"Sorted Rows"

    !! you need to lowercase your lookuptable as well !!


    Imke Feldmann TheBIccountant.com



    Wednesday, March 16, 2016 4:24 PM
  • Not meant to overwhelm or confuse, and like Imke, I too have created a solution that builds on Bertrand's solution. The main difference is that I've preserved the original "List of SM_'ICTI'" column per your request (i.e including the rows with no price, plus the original values in the column). In the following, the table named SM_ICTI is your original range converted to a table, without the price column (since this column just illustrates the result you want to achieve with Power Query).  The PriceLookup name used in the merged step is your Reference range converted to a table, and imported in Power Query.

    let
        Source = Excel.CurrentWorkbook(){[Name="SM_ICTI"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "List of 'SM_ICTI'", "List of 'SM_ICTI' - Copy"),
        SplitColumnByDelimiter = Table.SplitColumn(DuplicatedColumn,"List of 'SM_ICTI' - Copy",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"List of 'SM_ICTI' - Copy.1", "List of 'SM_ICTI' - Copy.2", "List of 'SM_ICTI' - Copy.3", "List of 'SM_ICTI' - Copy.4"}),
        UnpivotedOtherColumns = Table.UnpivotOtherColumns(SplitColumnByDelimiter, {"CR_Nr", "List of 'SM_ICTI'"}, "Attribute", "Value"),
        TrimmedText = Table.TransformColumns(UnpivotedOtherColumns,{{"Value", Text.Trim}}),
        SplitColumnbyDelimiter1 = Table.SplitColumn(TrimmedText,"Value",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Value.1", "Value.2"}),
        ExtractedQuantity = Table.TransformColumns(SplitColumnbyDelimiter1, {{"Value.2", each try Number.FromText(Text.Middle(Text.From(_, "en-US"), 0, Text.PositionOf(_, "[")-1)) otherwise null, type number}}),
        MergedQueries = Table.NestedJoin(ExtractedQuantity,{"Value.1"},PriceLookup,{"Unit"},"NewColumn",JoinKind.LeftOuter),
        ExpandedNewColumn = Table.ExpandTableColumn(MergedQueries, "NewColumn", {"Price"}, {"NewColumn.Price"}),
        AddedCustom = Table.AddColumn(ExpandedNewColumn, "Custom", each if [Value.2] = null then 0 else [Value.2] * [NewColumn.Price]),
        RemovedColumns = Table.RemoveColumns(AddedCustom,{"Attribute", "Value.1", "Value.2", "NewColumn.Price"}),
        GroupedRows = Table.Group(RemovedColumns, {"CR_Nr", "List of 'SM_ICTI'"}, {{"Total Price", each List.Sum([Custom]), type number}}),
        SortedRows = Table.Sort(GroupedRows,{{"CR_Nr", Order.Ascending}})
    in
        SortedRows


    Wednesday, March 16, 2016 4:51 PM
  • Because of one irritating value in your list of SM_ICTI (U-NA), I've had to modify the previous queries. U-NA doesn't match U-na in the lookup table, so I've modified the values in the columns for the Merged step to be uppercase.

    Thus for the SM-ICTI table, there is a new step just before the MergedQueries step:

    let
        Source = Excel.CurrentWorkbook(){[Name="SM_ICTI"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "List of 'SM_ICTI'", "List of 'SM_ICTI' - Copy"),
        SplitColumnByDelimiter = Table.SplitColumn(DuplicatedColumn,"List of 'SM_ICTI' - Copy",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"List of 'SM_ICTI' - Copy.1", "List of 'SM_ICTI' - Copy.2", "List of 'SM_ICTI' - Copy.3", "List of 'SM_ICTI' - Copy.4"}),
        UnpivotedOtherColumns = Table.UnpivotOtherColumns(SplitColumnByDelimiter, {"CR_Nr", "List of 'SM_ICTI'"}, "Attribute", "Value"),
        TrimmedText = Table.TransformColumns(UnpivotedOtherColumns,{{"Value", Text.Trim}}),
        SplitColumnbyDelimiter1 = Table.SplitColumn(TrimmedText,"Value",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Value.1", "Value.2"}),
        ExtractedQuantity = Table.TransformColumns(SplitColumnbyDelimiter1, {{"Value.2", each try Number.FromText(Text.Middle(Text.From(_, "en-US"), 0, Text.PositionOf(_, "[")-1)) otherwise null, type number}}),
        UppercasedText = Table.TransformColumns(ExtractedQuantity, {{"Value.1", each Text.Upper(_), type text}}),
        MergedQueries = Table.NestedJoin(UppercasedText,{"Value.1"},PriceLookup,{"Unit"},"NewColumn",JoinKind.LeftOuter),
        ExpandedNewColumn = Table.ExpandTableColumn(MergedQueries, "NewColumn", {"Price"}, {"NewColumn.Price"}),
        AddedCustom = Table.AddColumn(ExpandedNewColumn, "Custom", each if [Value.2] = null then 0 else [Value.2] * [NewColumn.Price]),
        RemovedColumns = Table.RemoveColumns(AddedCustom,{"Attribute", "Value.1", "Value.2", "NewColumn.Price"}),
        GroupedRows = Table.Group(RemovedColumns, {"CR_Nr", "List of 'SM_ICTI'"}, {{"Total Price", each List.Sum([Custom]), type number}}),
        SortedRows = Table.Sort(GroupedRows,{{"CR_Nr", Order.Ascending}})
    in
        SortedRows

    In the PriceLookup Table the query is:

    let
        Source = Excel.CurrentWorkbook(){[Name="PriceLookup"]}[Content],
        UppercasedText = Table.TransformColumns(Source, {{"Unit", each Text.Upper(_), type text}})
    in
        UppercasedText

    Wednesday, March 16, 2016 6:56 PM

All replies

  • Using a combination of 'split column' and 'unpivot' statements, you can isolate the references and the count. Then it is a matter of merging with the reference table to obtain what you want.
    Though not the complete solution, the code below should get you in the right direction:
    let
        Source = Excel.Workbook(File.Contents("C:\temp\Downloads\Query.xlsx"), null, true),
        Summmary_Sheet = Source{[Item="Summmary",Kind="Sheet"]}[Data],
        #"Removed Top Rows" = Table.Skip(Summmary_Sheet,1),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
        #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [#"List of 'SM_ICTI' "] <> "No SM or ICTI !!!"),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","List of 'SM_ICTI' ",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"List of 'SM_ICTI' .1", "List of 'SM_ICTI' .2", "List of 'SM_ICTI' .3", "List of 'SM_ICTI' .4", "List of 'SM_ICTI' .5", "List of 'SM_ICTI' .6"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List of 'SM_ICTI' .1", type text}, {"List of 'SM_ICTI' .2", type text}, {"List of 'SM_ICTI' .3", type text}, {"List of 'SM_ICTI' .4", type text}, {"List of 'SM_ICTI' .5", type text}, {"List of 'SM_ICTI' .6", type text}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CR_Nr", "Total Price"}, "Attribute", "Value"),
        #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim}}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text","Value",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Value.1"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1","Value.1",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Value.1.1", "Count"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Value.1.1", type text}, {"Count", Int64.Type}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type2",{"Value.1.1"},Reference,{"Unit"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Price"}, {"Price"})
    in
        #"Expanded NewColumn"

    Tuesday, March 15, 2016 5:35 PM
  • Hi Bertrand,

    Thanks a lot for your valuable suggestion.

    I want to know, if I need to lookup the Total price value from another sheet than , How can I proceed further with lookup function ?

    Thanks in advance for your valuable suggestion.

    Regards,

    Rajender

    Wednesday, March 16, 2016 3:57 PM
  • Hi Rajender,

    you can try this code, that builds on Bertrands very smart solution (nice trick when splitting "]" :-)):

    let
        Source = Excel.CurrentWorkbook(){[Name="Summary"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"CR_Nr", Int64.Type}, {"List of 'SM_ICTI' ", type text}, {"Total Price", type any}}),
        Index = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Filtered Rows" = Table.SelectRows(Index, each ([Total Price] <> "No SM or ICTI !!!")),
        SplitCriteria = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Split([#"List of 'SM_ICTI' "], "|")),
        ExpandSingleCriteria = Table.ExpandListColumn(SplitCriteria, "Custom"),
        ExtractRelevantPart = Table.SplitColumn(ExpandSingleCriteria,"Custom",Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv),{"Custom.1"}),
        SplitLookupFromMultiply = Table.SplitColumn(ExtractRelevantPart,"Custom.1",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Custom.1.1", "Custom.1.2"}),
        ChangeType = Table.TransformColumnTypes(SplitLookupFromMultiply,{{"Custom.1.1", type text}, {"Custom.1.2", Int64.Type}}),
        TrimCleanLookupString = Table.TransformColumns(ChangeType,{{"Custom.1.1", Text.Trim}}),
        LowercaseLookupString = Table.TransformColumns(TrimCleanLookupString,{{"Custom.1.1", Text.Lower}}),
        MergeLookupTable = Table.NestedJoin(LowercaseLookupString,{"Custom.1.1"},Reference,{"Unit"},"NewColumn",JoinKind.LeftOuter),
        ShowValues = Table.ExpandTableColumn(MergeLookupTable, "NewColumn", {"Price"}, {"Price"}),
        Multiply = Table.AddColumn(ShowValues, "Multiply", each List.Product({[Custom.1.2], [Price]}), Int64.Type),
        GroupByItem = Table.Group(Multiply, {"Index"}, {{"Result", each List.Sum([Multiply]), type number}}),
        MergeFilteredRowsBack = Table.NestedJoin(Index,{"Index"},GroupByItem,{"Index"},"NewColumn",JoinKind.LeftOuter),
        ShowResult = Table.ExpandTableColumn(MergeFilteredRowsBack, "NewColumn", {"Result"}, {"Result"}),
        #"Sorted Rows" = Table.Sort(ShowResult,{{"Index", Order.Ascending}})
    in
        #"Sorted Rows"

    !! you need to lowercase your lookuptable as well !!


    Imke Feldmann TheBIccountant.com



    Wednesday, March 16, 2016 4:24 PM
  • Not meant to overwhelm or confuse, and like Imke, I too have created a solution that builds on Bertrand's solution. The main difference is that I've preserved the original "List of SM_'ICTI'" column per your request (i.e including the rows with no price, plus the original values in the column). In the following, the table named SM_ICTI is your original range converted to a table, without the price column (since this column just illustrates the result you want to achieve with Power Query).  The PriceLookup name used in the merged step is your Reference range converted to a table, and imported in Power Query.

    let
        Source = Excel.CurrentWorkbook(){[Name="SM_ICTI"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "List of 'SM_ICTI'", "List of 'SM_ICTI' - Copy"),
        SplitColumnByDelimiter = Table.SplitColumn(DuplicatedColumn,"List of 'SM_ICTI' - Copy",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"List of 'SM_ICTI' - Copy.1", "List of 'SM_ICTI' - Copy.2", "List of 'SM_ICTI' - Copy.3", "List of 'SM_ICTI' - Copy.4"}),
        UnpivotedOtherColumns = Table.UnpivotOtherColumns(SplitColumnByDelimiter, {"CR_Nr", "List of 'SM_ICTI'"}, "Attribute", "Value"),
        TrimmedText = Table.TransformColumns(UnpivotedOtherColumns,{{"Value", Text.Trim}}),
        SplitColumnbyDelimiter1 = Table.SplitColumn(TrimmedText,"Value",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Value.1", "Value.2"}),
        ExtractedQuantity = Table.TransformColumns(SplitColumnbyDelimiter1, {{"Value.2", each try Number.FromText(Text.Middle(Text.From(_, "en-US"), 0, Text.PositionOf(_, "[")-1)) otherwise null, type number}}),
        MergedQueries = Table.NestedJoin(ExtractedQuantity,{"Value.1"},PriceLookup,{"Unit"},"NewColumn",JoinKind.LeftOuter),
        ExpandedNewColumn = Table.ExpandTableColumn(MergedQueries, "NewColumn", {"Price"}, {"NewColumn.Price"}),
        AddedCustom = Table.AddColumn(ExpandedNewColumn, "Custom", each if [Value.2] = null then 0 else [Value.2] * [NewColumn.Price]),
        RemovedColumns = Table.RemoveColumns(AddedCustom,{"Attribute", "Value.1", "Value.2", "NewColumn.Price"}),
        GroupedRows = Table.Group(RemovedColumns, {"CR_Nr", "List of 'SM_ICTI'"}, {{"Total Price", each List.Sum([Custom]), type number}}),
        SortedRows = Table.Sort(GroupedRows,{{"CR_Nr", Order.Ascending}})
    in
        SortedRows


    Wednesday, March 16, 2016 4:51 PM
  • Because of one irritating value in your list of SM_ICTI (U-NA), I've had to modify the previous queries. U-NA doesn't match U-na in the lookup table, so I've modified the values in the columns for the Merged step to be uppercase.

    Thus for the SM-ICTI table, there is a new step just before the MergedQueries step:

    let
        Source = Excel.CurrentWorkbook(){[Name="SM_ICTI"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "List of 'SM_ICTI'", "List of 'SM_ICTI' - Copy"),
        SplitColumnByDelimiter = Table.SplitColumn(DuplicatedColumn,"List of 'SM_ICTI' - Copy",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"List of 'SM_ICTI' - Copy.1", "List of 'SM_ICTI' - Copy.2", "List of 'SM_ICTI' - Copy.3", "List of 'SM_ICTI' - Copy.4"}),
        UnpivotedOtherColumns = Table.UnpivotOtherColumns(SplitColumnByDelimiter, {"CR_Nr", "List of 'SM_ICTI'"}, "Attribute", "Value"),
        TrimmedText = Table.TransformColumns(UnpivotedOtherColumns,{{"Value", Text.Trim}}),
        SplitColumnbyDelimiter1 = Table.SplitColumn(TrimmedText,"Value",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Value.1", "Value.2"}),
        ExtractedQuantity = Table.TransformColumns(SplitColumnbyDelimiter1, {{"Value.2", each try Number.FromText(Text.Middle(Text.From(_, "en-US"), 0, Text.PositionOf(_, "[")-1)) otherwise null, type number}}),
        UppercasedText = Table.TransformColumns(ExtractedQuantity, {{"Value.1", each Text.Upper(_), type text}}),
        MergedQueries = Table.NestedJoin(UppercasedText,{"Value.1"},PriceLookup,{"Unit"},"NewColumn",JoinKind.LeftOuter),
        ExpandedNewColumn = Table.ExpandTableColumn(MergedQueries, "NewColumn", {"Price"}, {"NewColumn.Price"}),
        AddedCustom = Table.AddColumn(ExpandedNewColumn, "Custom", each if [Value.2] = null then 0 else [Value.2] * [NewColumn.Price]),
        RemovedColumns = Table.RemoveColumns(AddedCustom,{"Attribute", "Value.1", "Value.2", "NewColumn.Price"}),
        GroupedRows = Table.Group(RemovedColumns, {"CR_Nr", "List of 'SM_ICTI'"}, {{"Total Price", each List.Sum([Custom]), type number}}),
        SortedRows = Table.Sort(GroupedRows,{{"CR_Nr", Order.Ascending}})
    in
        SortedRows

    In the PriceLookup Table the query is:

    let
        Source = Excel.CurrentWorkbook(){[Name="PriceLookup"]}[Content],
        UppercasedText = Table.TransformColumns(Source, {{"Unit", each Text.Upper(_), type text}})
    in
        UppercasedText

    Wednesday, March 16, 2016 6:56 PM
  • Hi Imke,

    Thanks a lot for your valuable assistance.

    Regards,

    Rajender

    Tuesday, March 22, 2016 2:22 PM
  • Hi Colin,

    Thanks a lot for your valuable assistance.

    Regards,

    Rajender

    Tuesday, March 22, 2016 2:23 PM
  • Hi Imke,

    Thanks a lot for your valuable assistance.

    Regards,

    Rajender

    Tuesday, March 22, 2016 2:23 PM