Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Combing tables in power query

I have three tables: 

Year: 

IDYEAR
12020
22021
32022


Business_unit: 

IDBusiness Unit
125FRANCE
243GERMANY
665UK


Status: 

IDStatus
45ZCompleted
85POn Hold
24YIn progress


What I would like to achieve to import those tables into a PBI report and then creating a table within my report and that table will be a kind of combination table, which will store the possible outcome of the three tables' IDs and will contain one column, like this: 

Combination (Year.ID & Business_Unit.ID & Status.ID)
112545Z
212585P
312524Y
124345Z
224385P
324324Y
166545Z
266585P
366524Y

 

Is that doable in power query? 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    3 years ago

    Hi Anonymous 

    Thanks for reaching out to us.

    please try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDJQitWJVjKCcAzBHGMIx0gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, YEAR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"YEAR", Int64.Type}}),
    ID1 = #"Changed Type"[ID],
    #"Converted to Table" = Table.FromList(ID1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Business_unit[ID]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Status[ID]),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type text}, {"Custom", type text}, {"Column1", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.2", each [Column1]&[Custom]&[Custom.1]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Expected Output"}})
in
    #"Renamed Columns"

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • TonyZhou1980's avatar
    TonyZhou1980
    Icon for Resolver I rankResolver I
    3 years ago

    let
    Source = Excel.CurrentWorkbook(),
    SelectedRows = Table.SelectRows(Source, each ([Name] = "Year")),
    Table1 = SelectedRows[Content]{0},
    PromoteHeader = Table.PromoteHeaders(Table1, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromoteHeader,{{"ID", type text}, {"YEAR", type text}}),
    Table2 = Table.FromColumns({List.Transform(List.Zip({ ChangedType[ID],Status[ID]}),each _{0} & "," & _{1}), ChangedType[YEAR],Status[Status]},{"ID","YEAR","Status"}),
    Table3 = Table.AddColumn(Table2,"A",each
    Table.AddColumn(Table.TransformColumns(BusinessUnit,{"ID",(x)=> Text.Replace([ID],",",x)}),"Year",(y)=>[YEAR])
    ),
    RemoveC = Table.RemoveColumns(Table3,{"ID", "YEAR"}),
    ExtendColumn = Table.ExpandTableColumn(RemoveC, "A", {"ID", "Business Unit", "Year"}, {"ID", "Business Unit", "Year"}),
    SortedC = Table.ReorderColumns(ExtendColumn,{"ID", "Business Unit", "Year", "Status"}),
    Final = Table.Sort(SortedC,{{"Business Unit", Order.Ascending}, {"ID", Order.Ascending}})
    in
    Final

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous 

    Thanks for reaching out to us.

    please try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDJQitWJVjKCcAzBHGMIx0gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, YEAR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"YEAR", Int64.Type}}),
    ID1 = #"Changed Type"[ID],
    #"Converted to Table" = Table.FromList(ID1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Business_unit[ID]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Status[ID]),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type text}, {"Custom", type text}, {"Column1", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.2", each [Column1]&[Custom]&[Custom.1]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Expected Output"}})
in
    #"Renamed Columns"

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.