Forum Discussion

jeffrey759's avatar
jeffrey759
Regular Visitor
8 years ago
Solved

Combine multiple columns

Hello,   I have multiple Vicinity's with a rating (number), but every Vicinity and rating exists as a new column. For this, I would like to have: 1 Column with the Vicinity and 1 column with the c...
  • ChandeepChhabra's avatar
    ChandeepChhabra
    8 years ago

    Hi jeffrey759

     

    Try the following code. Note that I have converted your data into a table (named vicinity) in Excel and wrote PQ code there

     

     

    let
Source = Excel.CurrentWorkbook(){[Name="Vicinity"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Calculated Modulo", "Custom", each if [Index] = 0 then [Column1] else null ),
#"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Index] = 1)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Vicinity"}, {"Custom", "Rating"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Rating", type text}, {"Vicinity", type text}})
in
#"Changed Type"

     

    Here is the snapshot of ther result


     

     

     

     

     

     

     

     

     

     

    You can download my excel (powerquery) file here

     

    thanks

     

  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    jeffrey759

     

    Please refer to my sample data screenshot. The column name format in my smaple data is like: Vicinity.0, Rating.0 and Vicinity.1, Rating.1 and so on. So that I can know which rating belonds to which vicinity. Also, I have a group column called Item. With this if there exists multiple groups, we can know which vicinities or ratings are in the same row.

     

    Thereby, in your scenario, your column names are like Vicinity, Vicinity_1, Rating_2 ... It is hard to know which rating belongs to which vicinity. Please format your sample data first by renaming the column names. And consider if it is necessary to add a group column.

     

    Thanks,
    Xi Jin.

jeffrey759's avatar
jeffrey759
Regular Visitor
8 years ago

Hi,

 

Already thank you but I can't get it to work (I'm quite new to this).
The goal is to have a file, that updates through an API from Google Business.

If i try the codes, I keep getting an error :(

This is what I actually got:

 

And this is my full code:

let
Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/details/json?placeid=ChIJx-b_oGbmw0cR0k3Q_aWKvNE&key=AIzaSyCe7SkWebBudQqBSR2pndsr1Q9lcqcVEIs")),
result = Source[result],
#"Converted to Table" = Record.ToTable(result),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "rating" or [Name] = "vicinity")),
#"Appended Query" = Table.Combine({#"Filtered Rows", Brugge, Hannut, Mons, Couillet, Poincaré, Vilvoorde, Genk}),
#"Reversed Rows" = Table.ReverseRows(#"Appended Query"),
#"Transposed Table" = Table.Transpose(#"Reversed Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"vicinity", type text}, {"rating", type number}, {"vicinity_1", type text}, {"rating_2", type number}, {"vicinity_3", type text}, {"rating_4", type number}, {"vicinity_5", type text}, {"rating_6", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"rating", type text}, {"rating_2", type text}, {"rating_4", type text}, {"rating_6", type text}, {"rating_8", type text}, {"rating_10", type text}, {"rating_12", type text}, {"rating_14", type text}})
in
#"Changed Type1"

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

jeffrey759

 

Please refer to my sample data screenshot. The column name format in my smaple data is like: Vicinity.0, Rating.0 and Vicinity.1, Rating.1 and so on. So that I can know which rating belonds to which vicinity. Also, I have a group column called Item. With this if there exists multiple groups, we can know which vicinities or ratings are in the same row.

 

Thereby, in your scenario, your column names are like Vicinity, Vicinity_1, Rating_2 ... It is hard to know which rating belongs to which vicinity. Please format your sample data first by renaming the column names. And consider if it is necessary to add a group column.

 

Thanks,
Xi Jin.