How to join multiple columns in a single query? (M language)

[Anonymous]

Hello,

I have several tables with the same structure, from different months. The structure is as follows:

Column 1: Active

Column 2: Month (January, February, ....)

What I want is a new query, with the following structure:

Column 1: Active

Column 2: January

Column 2: February

Column 3: March

....

Is it possible to do it in a single combination?

Thank you very much and best regards.

[Greg_Deckler · Accepted solution]

Not exactly what you are going for but maybe gets you close? What value is supposed to go into the month columns?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzCtNLKpUitWJVjIC8t1Sk4rgAsZAAd/EouQMMM8EyHMsKMrMUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Active = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Active", Int64.Type}, {"Month", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Month]), "Month", "Active", List.Sum)
in
    #"Pivoted Column"

You would need to append your tables together. If you could post sample data that would be helpful. 

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

The Active columns in different tables are same, or have some common values? If it is, you can merge the different tables into one. For example, merge the Table1 and Table2.

Table1Table2

I clieck Merge in Query Editor as follows.



I expand all columns and remove the Table2.Active, I get the followsing result.



This is my Query statement.

let
    Source = Table.NestedJoin(Table1,{"Active"},Table2,{"Active"},"Table2",JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Active", "Month"}, {"Table2.Active", "Table2.Month"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Table2.Active"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Month", "Jan"}, {"Table2.Month", "Feb"}})
in
    #"Renamed Columns"

For merging or appending, please see detailed steps here: Append vs. Merge in Power BI and Power Query.

Best Regards,
Angelia

[Anonymous]

Hello @v-huizhn-msft,

It is great.

Thank you very much and best regards.

[Greg_Deckler · Accepted solution]

Not exactly what you are going for but maybe gets you close? What value is supposed to go into the month columns?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzCtNLKpUitWJVjIC8t1Sk4rgAsZAAd/EouQMMM8EyHMsKMrMUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Active = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Active", Int64.Type}, {"Month", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Month]), "Month", "Active", List.Sum)
in
    #"Pivoted Column"

You would need to append your tables together. If you could post sample data that would be helpful. 

[Anonymous]

Hello @Greg_Deckler,

It is great.

Thank you very much and best regards.