Forum Discussion
Need Help! Data transformation
I am trying to understand your code. Could you please explain what is Table4 in query.
Result= Table.SelectRows(Table4,each not Text.Contains(CurrentText, [Keywords to be searched for]...
Actually "Added Custom1" step is failing with error "The name Table4 was not recognized".
Please help me to understand and resolved.
Thanks,
Randhir Singh
You need to create a query called Table4 with the following code for the output to execute
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkst8kvMTVWK1YlWcskszoZzHAsKcjKTE0sy8/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Keywords to be searched for" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Keywords to be searched for", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Keywords to be searched for", "Keywords to be searched for"}})
in
#"Renamed Columns"Anonymous just wondering if you tried the code I posted.
Do you want the ID column to just work as an index column in the final table?
- Hi @tex628, ID is just for index.
That's how it would look once you create two queries with the codes that I provided you.Table 2 is the desired output.
Hi @smpa01,
Thanks for your time and efforts. This is what I am looking for a dynamic approach. Actually I have total total 40 columns ( COL1,COL_Value, COL2,COL2_Value.... COL20, COL20_Value) .
Your solution is working fine as per sample data posted on web.
But still I am not able to understand how TABLE4 works.
It would be really great if you can explain me working of TABLE4.
Sorry,I am new to Power BI, could you please consider the table below as your Excel source and provide me query.
I'll modify your query for others remaining columns and try at my real data. Please note, given below sample data is containing all the possible scenerios.
ID COL1 COL1_Value COL2 COL2_Value COL3 COL3_Value COL4 COL4_Value COL5 COL5_Value 1 ServerType str6val Application str12val Metric str18val Perimeter str22val SubServerType str1val 2 ServerType str7val ServerType str13val Metric str19val Perimeter str23val SubServerType str2val 3 ServerName str8val ServerName str14val Application str20val Metric str24val SubServerType str3val 4 ServerName str9val ServerName str15val ServerType str21val Application str25val Perimeter str4val 5 ServerName str10val ServerName str16val Metric str5val 6 DiskName str11val ServerName str17val Metric str6val Thanks,
Randhir
Table4 is created to replicate this -
Hi,
This M code generates the table below
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"COL1", type text}, {"COL1_Value", type text}, {"COL2", type text}, {"COL2_Value", type text}, {"COL3", type text}, {"COL3_Value", type text}, {"COL4", type text}, {"COL4_Value", type text}, {"COL5", type text}, {"COL5_Value", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"), #"Uppercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Upper, type text}}), #"Added Custom" = Table.AddColumn(#"Uppercased Text", "Custom", each if Text.End([Attribute],5)="VALUE" then [Value] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if Text.End([Attribute],5)="VALUE" then null else [Value]), #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Custom.1"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Custom] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Value"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom.2", each Number.ToText([ID])&[Custom.1]), Partition = Table.Group(#"Added Custom2", {"Custom.2"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Custom", "Custom.1", "Index"}, {"ID", "Custom", "Custom.1", "Index"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Partition",{"Custom.2"}), #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Custom.2", each [Custom.1]&Number.ToText([Index])), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"Custom.1", "Index"}), #"Sorted Rows" = Table.Sort(#"Removed Columns2",{{"ID", Order.Ascending}, {"Custom.2", Order.Ascending}}), #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Custom.2]), "Custom.2", "Custom") in #"Pivoted Column"
