Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello to all,
I am trying to builds a category list for a future relationship in my model from the input of the first file and would like to have it as displayed in the second picture. I would like to achieve this in power query. Can someone help ?
Thanks
Solved! Go to Solution.
Hi @arcall,
Follow the steps below:
if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then null else [Account]
if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then [Category] else null
Check the complete M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc4xDsIwDAXQq1iZGRxIVTgAA3PHqEOEbSQKjdRESo+PSwnqUCnT+7bzvTddlFzCxHAb7/HNpj944/CIiHCdM09jeEEXH7kGzTZIugxpjdqzbYBSGJ40pFDtpCYkRbiKQyAqLPQb2ivw/2BjLV6WUkx10aFdQA9JUlzNfpsr6ds/1X8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then null else [Account]), #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Valid", each if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then [Category] else null), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Valid] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Valid"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Account", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Account.1", "Account.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Account.1", Int64.Type}, {"Account.2", type text}}) in #"Changed Type1"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @arcall,
Follow the steps below:
if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then null else [Account]
if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then [Category] else null
Check the complete M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc4xDsIwDAXQq1iZGRxIVTgAA3PHqEOEbSQKjdRESo+PSwnqUCnT+7bzvTddlFzCxHAb7/HNpj944/CIiHCdM09jeEEXH7kGzTZIugxpjdqzbYBSGJ40pFDtpCYkRbiKQyAqLPQb2ivw/2BjLV6WUkx10aFdQA9JUlzNfpsr6ds/1X8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then null else [Account]), #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Valid", each if Value.Is(Value.FromText(Text.Start([Account],1)), type number) = true then [Category] else null), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Valid] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Valid"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Account", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Account.1", "Account.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Account.1", Int64.Type}, {"Account.2", type text}}) in #"Changed Type1"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |