Forum Discussion

bgiri2430's avatar
bgiri2430
Frequent Visitor
3 years ago
Solved

Need to create table basee on two different table

I need to create a table C based on two different tables. Table A is having 10 column where table B is having 4 columns and there is no any matching key. Table A is a field parameter. Please assist?

Need Help
  • Mahesh0016's avatar
    Mahesh0016
    3 years ago

    bgiri2430 
    duplicate table A then create a Custom column in 
    Table.AddColumn(#"Added Custom", "Table B", each TableB) give table name after that expand custom column and get you output.Thank You!!

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hca5DQAhEATBXMbG4MfmOQhitfmncVKXVWaaCkoxyYNp8cw3L/zwyj/e+OWdPz7k/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Product ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Product ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Table B", each TableB),
    #"Expanded Table B" = Table.ExpandTableColumn(#"Added Custom", "Table B", {"Category", "Category Id"}, {"Category", "Category Id"})
in
    #"Expanded Table B"

    bgiri2430  I hope you getting my point.

5 Replies

      • Mahesh0016's avatar
        Mahesh0016
        Super User
        3 years ago

        bgiri2430 
        duplicate table A then create a Custom column in 
        Table.AddColumn(#"Added Custom", "Table B", each TableB) give table name after that expand custom column and get you output.Thank You!!

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hca5DQAhEATBXMbG4MfmOQhitfmncVKXVWaaCkoxyYNp8cw3L/zwyj/e+OWdPz7k/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Product ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Product ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Table B", each TableB),
    #"Expanded Table B" = Table.ExpandTableColumn(#"Added Custom", "Table B", {"Category", "Category Id"}, {"Category", "Category Id"})
in
    #"Expanded Table B"

        bgiri2430  I hope you getting my point.

  • bgiri2430's avatar
    bgiri2430
    Frequent Visitor
    3 years ago

    Mahesh0016 

    As I mentioned table A is field parameter. When we are moving to power query mode the table won't be available into power query that's the issue