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.
Hi everyone,
I am dealing with a situation and get stuck in this step. I divide my problem into 2 sub-problems.
1st sub-problem: as you can see in the first table below, there are columns without a header after a normal column. I want to add a new header for these empty-head-columns, which equals to [header of previous column]+"in Text". E.x: the second column will be "Column1 in Text" and so on, as you can see in the Expectation table.
2nd sub-problem: this data does not have headers on the same row (which are in column5, 6, 7). Therefore, I have tried to find a solution as IF RowIndex=1 & [ColumnX]="" THEN [ColumnX] = [ColumnX] WHERE RowIndex=0. Very sorry if my syntax is unprofessional.
Any help from you would be so valuable to me!
Thank you and wish you a nice week.
Solved! Go to Solution.
Hi @Anonymous ,
We can work it out in power query like that.
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSUQIiCMcYwgGhWJ1oJZCcERCDhE2A2BSIzcAy5kCWBRBbArGhAYgAqTU0UoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", Int64.Type}, {"(blank).2", type text}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Added Conditional Column" = Table.AddColumn(#"Transposed Table", "Custom", each if Text.Contains([Column1], "(blank)") then [Column2] else [Column1]),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1", "Column2"}),
#"Transposed Table1" = Table.Transpose(#"Removed Columns"),
#"Reversed Rows" = Table.ReverseRows(#"Transposed Table1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column1_1", Int64.Type}, {"Column3", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}})
in
#"Changed Type2"
Hi @Anonymous ,
We can work it out in power query like that.
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSUQIiCMcYwgGhWJ1oJZCcERCDhE2A2BSIzcAy5kCWBRBbArGhAYgAqTU0UoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", Int64.Type}, {"(blank).2", type text}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Added Conditional Column" = Table.AddColumn(#"Transposed Table", "Custom", each if Text.Contains([Column1], "(blank)") then [Column2] else [Column1]),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1", "Column2"}),
#"Transposed Table1" = Table.Transpose(#"Removed Columns"),
#"Reversed Rows" = Table.ReverseRows(#"Transposed Table1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column1_1", Int64.Type}, {"Column3", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}})
in
#"Changed Type2"
Covering 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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |