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 All,
my data is like
Column1 | Column2 | Coumn3 | col4 | col4 | col5 | col6 |
b | c | a | b | a | s | 1 |
c | a | n | c | d | 2 | |
f | ||||||
the output should be like this
Column1 | Column2 | Coumn3 | col4 | col4 | col5 | col6 |
b | c | a | b | a | s | 1 |
b | c | a | n | c | d | 2 |
c | a | n | c | f | 2 | |
f |
Please note I have a lot columns in my scenario, using if is not possible here like another postSolved: Fill up only one row - Microsoft Power BI Community
Tks in advance and happy lunar new year 🙂
Sample PBIX file attached
https://dropmefiles.com/JMSJC
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUUoG4kQgToLSxUBsqBSrE62kgCSbB2WnALERXBYdpwGxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Coumn3 = _t, col4 = _t, col4.1 = _t, col5 = _t, col6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}}),
Trans = Table.Transpose(#"Changed Type1"),
#"Changed Type2" = Table.TransformColumnTypes(Trans,{{"Column3", type text}}),
#"Replaced 1" = Table.ReplaceValue(#"Changed Type2"," ",(x)=> x[Column2],Replacer.ReplaceText,{"Column3"}),
#"Replaced 2" = Table.ReplaceValue(#"Replaced 1"," ",null,Replacer.ReplaceValue,{"Column4"}),
#"Added Custom" = Table.AddColumn(#"Replaced 2", "Custom", each [Column4]),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,(x)=> x[Column3],Replacer.ReplaceValue,{"Column4"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}})
in
#"Changed Type3"
fixed the code a bit
https://dropmefiles.com/kTKac
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUUoG4kQgToLSxUBsqBSrE62kgCSbB2WnALERXBYdpwGxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Coumn3 = _t, col4 = _t, col4.1 = _t, col5 = _t, col6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}}),
Trans = Table.Transpose(#"Changed Type1"),
#"Changed Type2" = Table.TransformColumnTypes(Trans,{{"Column3", type text}}),
#"Replaced 1" = Table.ReplaceValue(#"Changed Type2"," ",(x)=> x[Column2],Replacer.ReplaceText,{"Column3"}),
#"Replaced 2" = Table.ReplaceValue(#"Replaced 1"," ",null,Replacer.ReplaceValue,{"Column4"}),
#"Added Custom" = Table.AddColumn(#"Replaced 2", "Custom", each [Column4]),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,(x)=> x[Column3],Replacer.ReplaceValue,{"Column4"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}})
in
#"Changed Type3"
Hi @woshi_eads
you have options in querry editor choose fill down option . It will automatically updated the value.
Your should have the Numeric format. Then You can use. if have Text It won't Work.
Refference Pic:
Thanks,
Thennarasu
Hi @woshi_eads,
I think you can find your solution in this below links,
https://www.youtube.com/watch?v=90EYX7pzVlE
https://www.youtube.com/watch?v=Hc3d8rMSXcQ
Regards,
Nikhil Chenna
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |