Combing tables in power query

[Anonymous]

I have three tables: 

Year: 

ID	YEAR
1	2020
2	2021
3	2022

Business_unit: 

ID	Business Unit
125	FRANCE
243	GERMANY
665	UK

Status: 

ID	Status
45Z	Completed
85P	On Hold
24Y	In 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? 

[v-xiaotang · Accepted solution]

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.

[TonyZhou1980]

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