Forum Discussion
please help to change my code, and get the correct result table
my code
let
Source = tlb_location,
#"Merged Queries" = Table.NestedJoin(Source,{"location"},tlb_filter,{"filter"},"tlb_filter",JoinKind.RightOuter),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"tlb_filter"})
in
#"Removed Columns"
How can I change my code, and get correct table in right hand side.
I try to do again, the code below, how can change Blue Colour code into Dynamic Column Names
let
tlb_location = #table( { "location", "value" },
{{ "US UK", 1 }, { "I love US UK", "21" },{ "US UK", 1 }, { "UK", "13" },
{ "UK India", 41 }, { "Australia China", "15" }, { "", "61" }}),
tlb_filter = Table.Transpose(#table( { "filter"},{{"India"}, {"US UK"}} )),
Source = tlb_location,
#"Filtered Rows" = Table.SelectRows(Source, each ([location] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"location", type text}, {"value", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", tlb_filter}),
#"Filled Up" = Table.FillUp(#"Appended Query",{"Column1", "Column2"}),
#"Added Conditional Column" = Table.AddColumn(#"Filled Up", "location2", each if Text.Contains([location], [Column1]) then [location] else if Text.Contains([location], [Column2]) then [location] else null),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"location2", null}}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Errors", each ([location2] <> null))
in
#"Filtered Rows1"
I change the code again below, the problem is how to sort Column 1 to 3 by dynamic out [location]
__
let
tlb_location = #table( { "location", "value" },
{{ "US UK", 1 }, { "I love US UK", "21" },{ "US UK", 1 }, { "UK", "13" },
{ "UK India", 41 }, { "Australia China", "15" }, { "", "61" }}),
tlb_filter = Table.Transpose(#table( { "filter" }, {{"India"}, {"US UK"}, {"China"}} )),
tlb_filter2 = Table.Transpose(tlb_filter),
Source = tlb_location,
#"Filtered Rows" = Table.SelectRows(Source, each ([location] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"location", type text}, {"value", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", tlb_filter}),
columnCount = Table.ColumnCount(tlb_filter),
columnList = List.Transform(
{0..columnCount-1},
each "Column" & Text.From(_ + 1)
),
#"Filled Up" = Table.FillUp(#"Appended Query", columnList)
in
#"Filled Up"